articles >> performance tuning >> Are SQL Server Temp Tables Really Necessary? ...

Are SQL Server Temp Tables Really Necessary?

By : Randy Dyess Dec 12, 2003

You will find articles on this website, 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 at the end of this article. While these issues are true, I'm going to provide some reason to use temporary tables. I will admit that I do not use or have not found a reason to use global temporary tables, so you will find a discussion 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 a query becomes 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 number 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.

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 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 where I work. It has been created to show a particular problem with a hypothetical telecommunications database.)