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.
How many query plans are possible with a different number of tables in a join?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.
To join, query tree can be:
- Left-Deep Tree
- Right-Deep Tree
- Bushy Tree
| Tables | Candidate PlansLeft-deep trees | Candidate Plans Bushy trees |
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 6 | 12 |
| 4 | 24 | 120 |
| 5 | 120 | 1,680 |
| 6 | 720 | 30,240 |
| 7 | 5,040 | 665,280 |
| 8 | 40,320 | 17,297,280 |
| 9 | 362,880 | 518,918,400 |
| 10 | 3,628,800 | 17,643,225,600 |
One more misconception among a small group of developers is that using simple views can resolve this problem. In fact it’s just an illusion.
Suppose that we have a View, which use five tables in its definition. If we join this view within our query then SQL Server will reopen this view with all its five tables and will use these to join with three tables of query and as a result 17,297,280 plans will be there for Query Optimizer to evaluate.
Summary
The number of tables in a query does matter. Keep this number as low as possible by dividing work among queries. Avoid over normalization of your data that will push you to have more tables in every other quer, and last but not least, tables within a simple view are added tothe final query, and simple views are not a solution. Even derived tables are not a solution for this problem.
Queries perform better when they are written according to Query Optimizer.
No comments:
Post a Comment