James Goodwin (6/3/2010)
calvo (5/7/2010)
So, now knowing how a query is processed internally, how can we use this knowledge to improve query performance or perhaps troubleshooting unexpected results?I think there are two standout things to learn from this:
1. Since JOIN happens before WHERE a more complete ON clause may be more efficient as there will be fewer rows to filter out in the WHERE clause.
This is not true for an INNER JOIN. The placement of the predicate has no influence on the generated query plan and the efficiency. (Exception - for very complicated many-table joins with little rows, the optimizer might stop searching for a better plan if it estimates that the cost of finding a better plan exceeds the cost saved by that plan; in that case the placement might affect performance, and that might happen in both ways. This is rare, though).
2. Since GROUP BY happens before SELECT and DISTINCT happens after SELECT. These:
a) SELECT DISTINCT A,B,C FROM TABLE
b) SELECT A,B,C FROM TABLE GROUP BY A,B,C
Are not necessarily going to have the same efficiency. This surprises me a little as my naive assumption would be that the optimizer would convert the DISTINCT into a GROUP BY.
Again, incorrect. These will have the same plan, as that conversion is exactly what the optimizer does.