Breaking down complex queries
As we saw in Chapter 3, The Query Optimizer, in some cases, the SQL Server query optimizer may not be able to produce a good plan for a query with a large number of joins. The same is true for complex queries with both joins and aggregations. However, because it is rarely necessary to request all the data in a single query, a good solution for those cases could be to just break down a large and complex query into two or more simpler queries while storing the intermediate results in temporary tables. Breaking down complex queries this way offers several advantages:
- Better plans: Query performance is improved because the query optimizer can create efficient plans for simpler queries.
- Better statistics: Because one of the problems of some complex plans is being able to degrade intermediate statistics, breaking down these queries and storing the aggregated or intermediate results in temporary tables allows SQL Server to create new statistics, greatly...
