Hello
has anybody else noticed
I have queries that SQL 2000 optimizes correctly and they run very
fast
Yet SQL 2005 keeps using a dumb query plan and queries run very slow
The problem seems to stem from the assumption that data in a derived
table / subquery will not stay the same for different output rows
So the query is pointlessly re-evaluating the derived table again and
again
The same problem is also affecting subqueries
(This is how msaccess works - but that is not a real database server)
I have experimented with TABLOCKX but the optimizer still seems to
think data can change between rows
Am I missing something?
I am manually optimizing badly affected queries by rewriting them as
stored procedures with temporary tables
but that is like going back in time 20 years
The whole point in the optimizer is that it should find the most
intelligent way to return the results
John