| |
 |
|
SQL Server Tips by Burleson |
Temporary tables
Temporary tables are very useful for storing intermediate values
from time consuming calculations, store the output rows from a SP,
serve as a buffer for data waiting to be processed, avoid a cursor
with a heavy load, avoid repeating queries with a huge number of
rows or complex joins, an alternative to recursive solutions, etc.
Temporary tables are very effective with huge amounts of data. By
filtering only the strictly necessary rows and columns and storing
the result in a temporary table, for several gigantic tables, this
will create mini versions, easier to use. Doing this before a SELECT
statement with a JOIN clause that would use those temporary tables
and with carefully chosen indexes, the result will be a great
performance boost.
One of the downsides is that it creates locks on tempdb.
It is recommended to avoid SELECT INTO with temporary tables because
it locks system objects causing performance degradation. Extra care
is required with clustered indexes to make sure that the extra
overhead will not outweigh the advantages.
There are three kinds of temporary tables: local, global and table
datatype.
The above book excerpt is from:
Super SQL
Server Systems
Turbocharge Database Performance with C++ External Procedures
ISBN:
0-9761573-2-2
Joseph Gama, P. J. Naughter
http://www.rampant-books.com/book_2005_2_sql_server_external_procedures.htm |
