How Can You Work Around Using a Temporary Table?
Now that I shown you several situations when you consider using a temporary table, let’s talk about what you can do to avoid using a temporary table if possible.
There is a nice thing in the SQL world, called a derived table, that can be used to replace temporary tables in most cases. Once again, I’ll get on my performance soapbox and say that sometimes with very large data sets, derived tables performance is considerably less than using a temporary table with an index. But for most cases, simply using a derived table on a join will cut the need for your temporary table.
You can find several articles on the use of derived table at either WWW.SQLServerCentral.Com or WWW.SQL-Server-Performance.Com, so I will not go into detail on their use in this article.
If you are using a temporary table to stage data from several different sources, either replace the temporary table with a UNION, or create a permanent table to mimic the temporary one, both will usually satisfy your needs with reduced overhead. If you are operating on SQL Server 2000 and are using small data sets, try using the new table data type. This will create a temporary table like object in memory rather than in the tempdb, and improve the performance of your query. Also, explore the use of a correlated sub-query and see if it can replace your temporary table. Sometimes just restating where your data is coming from will replace the need for temporary tables.
Any one of these ways has been discussed as possible alternative solutions to the use of a temporary table. The main key is for you to test alternative ways to determine if you can replace the use of a temporary table before you settle in a create one out of habit. As you create your bag or tricks you will find yourself using temporary tables less and less and even find yourself disgusted at your coding abilities when you actually have to use a temporary table when you truly believe there is another way out there.
If You Do Use Temporary Tables, Optimize Their Use
If the situation mandates the use of a temporary table, then there are several things you can do to maximize their performance.
First, just because it is a temporary table do not be tempted to put all the columns and all the rows from your permanent table into the temporary table if you do not need them. Filter the data going into your temporary table to include the minimum number of columns and rows actually needed.
Second, do not use the SELECT INTO statement to create your temp table. The SELECT INTO should be avoided at all costs in your coding due to the locking it places on system objects while it determines how to build the table. Take the time to script the temporary table out and use a separate INSERT INTO to populate the table. I will qualify this with that you can use a SELECT INTO if it includes WHERE 1=0 to create a table in the quickest way possible, but don’t do this just to save a few keystrokes.
Third, watch how you use temporary tables to avoid recompiles on the stored procedure. I explain this in getter detail in my article Optimizing Stored Procedure Recompiles.
Fourth, test the need for a clustered-index on your temporary table. If the data set is large, a cluster-index will speed the operations against the temporary table, but you have to weigh the performance needs of creating that index and inserting data into a table with a clustered-index. This is one of those methods that needs to be tested both ways, with the largest data set you think will be placed into the temporary table, before deciding on the index.
And last, I know that when the stored procedure completes and the connection ends, the temporary table will be dropped, but why keep it around if you are done with it. If you code creates and uses a temporary table, and then goes on to do other things that do not involve that table — drop the table when you are done. This frees up tempdb resources for other objects. I will even drop the table at the end of a stored procedure even though the connection is about to finish just to avoid any issues that may arise with unknown bugs.
Summary
While temporary tables (in my opinion) are far better than cursors, they do cause a performance hit when being used. This article has briefly discussed several reasons to use a temporary table and several methods to use as alternatives to temporary tables.
The key remains you and your situation. Test your query with alternatives before you create a temporary table, and test your performance hogs created with temporary tables before you decide on what you can and can’t do. I strongly believe that even though I am writing this article, it is my opinion, based on my history and before I even jump in with something I read in a book or web site, I will test it several different ways. Do this and your Transact-SQL skills will continue to grow to the point that you always have several different paths to take to create a query.
Copyright 2002 by Randy Dyess, All Rights Reserved
You can also view more articles by the authors at www.TransactSQL.Com
]]>