TheSQLGuru (9/4/2009)
1) it doesn't have to be a bug if the optimizer for 2000 derives a suboptimal query plan for a complex query involving 15 tables in a mishmash of views.
Agreed. The optimiser's job is not to find the best plan. Never has been. It's job is to find a good plan fast and it's only allowed a certain amount of time to find said plan. If it doesn't identify a 'good enough' plan by the time the time limit is up, it'll return with the best plan found up til that point. Which can be very bad indeed.
2) Gail is correct in that it can often truly benefit the optimizer to subdivide large queries such as this into smaller interim steps using temp tables (NOT table variables) because each step along the way can then have it's own set of statistics that prevents small skews from becoming large query performance problems.
Also because the optimiser gets to work on several smaller queries rather than one massive great big one. The search-space for the plans is smaller (much smaller) and the chance that it'll find a good enough plan is a lot better.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability