- “Where possible ALWAYS have you smallest tables joined to first.”
- “Where possible ALWAYS have you largest tables joined to first”
So already you might start to doubt the validity of the rule :-) GOOD
I’ve created 5 tables all with the same structure.
I’ve added different amounts of data to each table as follows:
I then created 6 queries all with various join orders:
Each Query returns 100 rows as expected and using :
I get the same result for each query: (I ran each query over 10times):
SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms. (100 row(s) affected) SQL Server Execution Times: CPU time = 0 ms, elapsed time = 3 ms.
For some people this might seem strange as they may have been taught that changing the order of your joins can dramatically affect your result.
Now let’s look at the execution plans:
OK so this is all very strange isn’t it!!!
If you run Queries 1 -3 together you get this:
Query 1: Query cost (relative to the batch) 55%
Query 2: Query cost (relative to the batch) 13%
Query 3: Query cost (relative to the batch) 33%
If you run Queries 4 -6 together you get this:
Query 4: Query cost (relative to the batch) 33%
Query 5: Query cost (relative to the batch) 33%
Query 6: Query cost (relative to the batch) 33%
The reason the above percentages are different and why the percentages in the Query Plans for each Clustered Index Seek/Scan are different for each query is mainly due to the statistics on the tables. After running the following statement:
The Query Costs relative to the batch for each query became equal and the percentages for each operator in the different Execution plans became the same.
More importantly we notice that the Execution plans themselves are very much the same for each query.
At this stage I would like to point you to Grant Fritchey’s blog about a recent article Execution Plan Estimated Operator Cost for more information on the strange percentages in execution plans.
The most important thing to remember here is to understand what you are writing. Test what people tell you if you not sure, on top of this understand what an execution plan is showing you. Understand the plan don’t blindly believe the percentages …