Posted by Erland Sommarskog on 01/29/07 22:41

rbg (rbg.net@gmail.com) writes:
> I have one more question though.
> When I used Temp tables instead of derived tables, my performance
> improved significantly. The query which takes 30 seconds using the
> derived tables, takes only 11 seconds when using Temp tables.
>
> So is it better to choose the Temp table solution over the derived
> tables solution?

So instead of the derived tables, you did the SELECT TOP into the
temp tables? Or did you use the temp tables in any other way?

Assuming the first, this is one of those hairy questions of which the
short answer is "it depends". In most cases, it's more effecient to do
all in one query, rather than matierialising the intermediate results
in a temp table. This is because the optimizer may find ways to recast
the computation order without affecting the final result. But there are
always exceptions. In this particular case, the optimizer may not have
been able to find a shortcut. On the other hand, the temp table has
statistics, so when coming to the next query, the optimizer has more
information and may find a better plan.

I'm glad to hear that sp_executesql resolved your problems!

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]