There are two common approaches to working with large queries derived from other queries. One is to create a nested query, a SELECT within a SELECT, which derives the results of one query from another.
Another is to SELECT records into a temporary table, then perform another SELECT on the temporary table to further cull the results. This is a multi-step operation that requires creating, using and then dropping the temporary table.
The first approach is best where there is plenty of memory available to run fairly large queries and there is little chance of the query's complexity creating a timeout. Nested queries have the advantage of being run almost exclusively in physical memory for the sake of speed, unless the amount of memory needed to perform the query exceeds the physical memory "footprint" allocated to SQL Server.
Also, because of a nested query's structure, the SQL Server query optimizer will attempt to find the most optimal way to derive the results from the query. A temporary-table method may require several transactions, which is inherently slower. Temporary tables also have to be dropped at the end of the transaction manually, even if the query has timed out or failed in some other way.
However, temporary tables have a few advantages that nested queries don't. Because using temporary tables for derived operations are multi-step operations, there are more opportunities for the user to be kept abreast of the progress of things. For instance, if the process is performed from an ASP page, with each step in the process being a separately-dispatched SQL statement, the user can be kept continually informed at what stage the whole thing is at. This is especially useful if several SELECT operations are needed to completely populate the temporary table, which can extend the total time needed to process a simple nested query way beyond the timeout limit. If raw speed is not absolutely of the essence, or if it can be ameliorated with continual user feedback, this is a good way to proceed.
A side note: Using transient permanent tables for temporary data is never a good idea. Permanent tables require writes to the transaction log, which incur a performance hit.
Serdar Yegulalp is the editor of the Windows 2000 Power Users Newsletter. Check out his Windows 2000 blog for his latest advice and musings on the world of Windows network administrators – please share your thoughts as well!
