Thank this author by sharing:
By Randy Dyess,
2008/05/30 (first published: 2002/04/09)
You will find articles on this site as well as others advising you to avoid the use of temporary tables to
maximize the performance of your queries. I agree with the articles, but would like to add that sometimes you
cannot avoid the use of a temporary table. For those of you who have not read my Bio, I work with some very
large SQL Server databases (the biggest being over 2.2 terabytes) and have found that I can avoid the use of
temporary tables in most cases but sometimes they come in handy. This article will discuss the use and the
alternatives to temporary tables from a query performance and maintenance standpoint.
Most of the literature that advises against the use of temporary correctly states that they may cause performance
issues due to the locking of the tempdb while the temporary table is being created, the I/0 activity
involved during the use of the temporary table, and the potential locking of the tempdb if a transaction
is used for the creation and the subsequent operations against the temporary table, not to mention the numerous
problems SQL Server has with operations against temporary tables - see the list of Knowledge Base articles
below. While these issues are true, I'm going to provide some reason to use a temporary table. I will admit that
I do not use or have found a reason to use a global temporary table so you will find discussions on global temporary
tables absent in this article.
Why would you use a temporary table?
There are several reasons that I use temporary tables in my work; to hold the results of a called stored procedure,
to reduce the number of rows for joins, to aggregate data from different sources, or to replace cursors.
As your query become more complex you will find yourself repeating blocks of code within a query or between
different queries. This reuse of code makes the case for a creating a stored procedure with that code and calling
that stored procedure. This may make for a large amount of stored procedures in your database, but it does greatly
reduce the maintenance when the functionality needs to be changed and you have to change code to meet that functionality,
only one query to change now and not multiple queries that have to researched and changed. I use this technique
quite often and it often forces me to use a temporary table to hold the results of those stored procedures since
Transact-SQL does not allow the results of a stored procedure to be used as a table. This is probably the number
one reason in my code for the use of temporary tables.
I quite often find myself having to join a 10 million plus row table to a 100 million plus row table to a
20 million plus row table and then ordering the results so only the most recent activity displays first.
Even with proper indexes and using WHERE clauses to filter and force the use of an index, the performance of
the query is unacceptable (since the application I work on is used by call centers, acceptable performance
for a query is measured in seconds) and often the sorting produces huge performance losses as well as huge
tempdb activity. I have quite often found that using corresponding temporary tables for each of the
permanent tables to hold data from filtered by the WHERE clauses before I join and sort the data will increase
performance on the query to such a large degree that I can actually place it into production without worrying
about its performance or the impact on the tempdb database. Below is a very simple query to show how I do this.
Original Query to find details on a particular customer's phone call
SELECT table1.numCustID, table2.strPhoneNumber, table3.strPhoneNumberCalled
FROM dbo.table1 table1
INNER JOIN dbo.table2 table2
ON table1.numBillID = table2.numBillID
INNER JOIN dbo.table3 table3
ON table2.numBillDtlID = table3.numBillDtlID
WHERE table1.numCustID = '5555'
AND table2.strPhoneNumber = '5555555555'
AND table3.strPhoneNumberCalled = '1234561234'
ORDER BY table3.dtmCalled DESC
(This query does not match the schema or an existing query at Verizon. It has been created to show a
particular problem with a hypothetical telecommunications database.)
(I usually name my temporary table after the stored procedure that created it so I can troubleshoot
any problems in tempdb from the use of temporary tables faster.)
CREATE TABLE #tquery2a
CREATE TABLE #tquery2b
INSERT INTO #tquery2a
SELECT columns FROM dbo.table2 WHERE table2.strPhoneNumber = '5555555555'
INSERT INTO #tquery2b
SELECT columns FROM dbo.table3 WHERE table3.strPhoneNumberCalled = '1234561234'
SELECT table1.numCustID, #tquery2a.strPhoneNumber, #tquery2b.strPhoneNumberCalled
FROM dbo.table1 table1
INNER JOIN #tquery2a #tquery2a
ON table1.numBillID = #tquery2a.numBillID
INNER JOIN #tquery2b #tquery2b
ON #tquery2a.numBillDtlID = #tquery2b.numBillDtlID
WHERE table1.numCustID = '5555'
ORDER BY #tquery2b.dtmCalled DESC
Believe it or not this method works, especially with the ORDER BY statement and its performance is vastly
better than the original query.
Reporting off an OLTP designed databases is not always the easiest thing to do. The database is just built
to maximize reports that executives want. Using temporary tables to stage the results from numerous SELECT
statements, aggregate those results before displaying them is sometimes the only way to can get reports out
of an OLTP database. Working in a call center application you are usually asked to produce reports that
summarize what the call center reps are doing on a time filtered basis. Working your way through all the tables
to gather the data and then summarizing it in multiple ways can only be accomplished with the use of
temporary tables. Before any comes up with this argument: I know I work in a multi-billion company but that
doesn't mean that executives are willing to listen to our arguments that they need a data warehouse or a
simple reporting database if it means they have to spend money to get one when they can just as easy piggy-back
off of the OLTP database and blame me if the queries are too slow and cause a performance headache for the
servers. Sorry, that was for the theoretical guys out there who have magically gotten everything they wanted
no matter the cost or the size of the companies they worked for.
The last argument for the use of a temporary table is to replace a cursor. I am not fond of cursors and advocate
doing anything possible to replace the cursor (performance of your solution needs to be tested against the
performance of the cursor though). One of the tricks I use is to mimic the main reason a cursor is usually built
for, looping through a result set one row at a time and performing an action based on the data in that row.
Below is a short query that displays this logic by obtaining all the user table names and executing sp_spaceused
on each table.
SET NOCOUNT ON
DECLARE @lngTabCount INTEGER
DECLARE @lngLoopCount INTEGER
DECLARE @strTabName SYSNAME
CREATE TABLE #tTables
numID INTEGER IDENTITY(1,1)
INSERT INTO #tTables (strTableName)
SELECT name FROM dbo.sysobjects WHERE xtype = 'u'
SET @lngTabCount = @@ROWCOUNT
SET @lngLoopCount = @lngTabCount
WHILE @lngLoopCount <> 0
SET @strTabName = (SELECT strTableName FROM #tTables WHERE numID = @lngLoopCount)
EXEC sp_spaceused @strTabName
SET @lngLoopCount = @lngLoopCount - 1
DROP TABLE #tTables
Cursor-like actions without cursor overhead and performance related problems.
How can you work around using a temporary table?
Now that I shown you several situations when you consider using a temporary table, lets talk about what you can
do to avoid using a temporary table if all 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 WWW.SQLServerCentral.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 on the tempdb and improve the performance of your query. 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 use temporary tables optimize their use.
If the situation mandates 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
available on my website. 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 in the performance needs of
creating that index and inserting into the 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.
While temporary tables (in my opinion) are far better than cursors, they do have 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
Knowledge Based Articles
Q198428 PRB: ODBC Prepared Statement Errors with Temporary Tables
Q234521 BUG: Inserting Data into Temporary Table's Identity Column in Stored Procedure Fails
Q269282 FIX: Error Message 602 Occurs When You Query a Temporary Table That Has a Non-Clustered Index
Q300701 BUG: BCP into Temporary Table Fails with Error 229 "INSERT Permission Denied"
Q159747 INF: Canceled Transaction in a Temp Table Causes X and U Locks
Q166200 BUG: Errors 2714 and 267 on INSERT INTO Global Temp Table
Q276499 FIX: SP2 Regression: Running a Stored Procedure with Index on Local Temp Table Causes AV
Q275721 FIX: Execution of Stored Procedure with Local Temp Table and Indexes May Fail or Return Incorrect Results
Q295305 PRB: Creating Temp Table Inside IF and ELSE Conditions Gives Error 2714
Q308817 FIX: Stored Procedure That Creates Temp Table [#a.b.c.d.e] Causes Access Violation
Q316203 BUG: INSERT of RESTORE HEADERONLY Results into Temp Table May Generate an Access Violation
Copyright 2002 by Randy Dyess, All rights Reserved
You can also view more of my articles at my personal web site: www.TransactSQL.Com
Temporary Tables performance issue
Temporary Stored Procedures - little known sql server feature
Improve the SQL query performance with temporary variables
When you create a temporary table you expect a new table with no past history (statistics based on p...
How can I create n temporary tables dynamically?
As a member of SQLServerCentral, you get free access to loads of fresh content: thousands
of articles and SQL scripts, a library of free eBooks, a weekly database news roundup,
a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals
that makes it such a success.