SQL Server Query Optimization - Day 2 (Number of Joins Does Matter)

• SQL Server Query Optimization - Day 1 (Basics)

Query Optimizer is designed in a way to keep a balance between the quality of a query and its execution time. Query Optimizer can’t take hours to evaluate all candidate plans in order to select the best one, but rather it is designed to try within a limited time and select optimum plan.
To select an optimum performing plan, the most important thing for Query Optimizer is “Join Order." At a single time, only two tables can be joined together, and thena third can be joined with the output from the first two tables, or it is possible to join the two tables separately and then join the result sets. Ordering depends on the nature of join. But which table should be accessed first and which will be joined later -- all these possible patterns are evaluated to get best one.

The number of expected plans (candidate plans) is directly proportional to the number of tables joined together in a query. Query Optimizer will always select best possible plan, if expected plans are very few and Query Optimizer is able to evaluate all possible plan within its limited time. On the other hand, if the database is over-normalized and more tables need to be joined, then there are hundreds or thousands of candidate plans and due to limited time Query Optimizer can evaluate a fewer number of plans, and there are possibilities that all evaluated plans were badly performing and Optimizer has selected the best way out of all the bad plans.

• Left-Deep Tree
• Right-Deep Tree
• Bushy Tree