Temp Table Performance Tuning Tips

In general, temp tables should be avoided, if possible. Because they are created in the tempdb database, they create additional overhead for SQL Server, slowing overall performance. As an alternative to temp tables, consider the following alternatives:

  • Rewrite your code so that the action you need completed can be done using a standard query or stored procedure, without using a temp table.
  • Use a derived table.
  • Consider using a table variable.
  • Consider using a correlated sub-query.
  • Use a permanent table instead.
  • Use a UNION statement to mimic a temp table.

[2000, 2005, 2008] Updated 1-29-2009

*****

One legitimate reason you might want to consider using a temp table is to avoid having to use a cursor. SQL Server cursors have huge overhead and slow SQL Server’s performance. One alternative of using a cursor is to use a temp table instead. In almost all cases, using a temp table over a cursor will produce less overhead and better performance. Of course, if you do not have to use a temp table, and find another way to get away from using a cursor, so much the better. [2000, 2005, 2008] Updated 1-29-2009

*****

SQL Server offers a data type called “table.” Its main purpose is for the temporary storage of a set of rows. A variable, of type “table,” behaves as if it is a local variable. And like local variables, it has a limited scope, which is within the batch, function, or stored procedure in which it was declared. In most cases, a table variable can be used like a normal table. SELECT, INSERT, UPDATE, and DELETE can all be made against a table variable.

If you need a temporary table in your Transact-SQL code, consider using a table variable instead of creating a conventional temporary table instead. Table variables are created and manipulated in memory instead of the tempdb database, making them faster in some cases. But not in all cases. Because of this, you will need to test both options to determine which works best for you under your particular circumstances.

In addition, table variables found in stored procedures result in fewer compilations (than when using temporary tables), and transactions using table variables only last as long as the duration of an update on the table variable, requiring less locking and logging resources. [2000, 2005, 2008] Updated 1-29-2009

*****

If you have no choice but to use a temp table, you can help to optimize its performance by taking one or more of the following steps:

  • Only include the columns and rows you actually need in the temp table, no more.
  • Do not use SELECT INTO to create your temp table, as it places locks on system objects. Instead, create the table using standard Transact-SQL DDL statements, and then use INSERT INTO to populate the table.
  • Consider using clustered and non-clustered indexes on your temp tables, especially for very large temp tables. You will have to test to see if indexes help or hurt overall performance.
  • When you are done with your temp table, delete it to free up tempdb resources. Do not wait for the table to be automatically deleted when the connection is ended.
  • If the tempdb database is not already on its own dedicated disk or array, consider taking this step. By isolating the tempdb database on its own disk, disk contention is reduced and performance is increased.

[2000, 2005, 2008] Updated 1-29-2009

*****

If you have to use a temp table, do not create it from within a transaction. If you do, then it will lock some system tables and prevent others from executing the same query, greatly hurting concurrency and performance. In effect, this turns your application into a single-user application.

To avoid this problem, create the temporary table before the transaction. This way, the system tables are not locked and multiple users will have the ability to run this same query at the same time, helping concurrency and performance. Contributed by Gaurav Bindlish. [2000, 2005, 2008] Updated 1-29-2009




Related Articles :

  • No Related Articles Found

2 Responses to “Temp Table Performance Tuning Tips”

  1. A lot of the tips in this page are actually pretty bad.

    As an example, a SELECT INTO will usually offer a better performance because it is a bulk operation. I would be curious to know exactly what “system objects” are locked during this operation.

    Also in order to maximize performance it is better NOT to drop a temporary table and to just let it run out of scope. The end result will be the same (table won’t exist anymore) but if you drop it inside your session then the transaction stays open until the delete is completed, while letting the table run out of scope will just flag it for removal and let the engine deal with it without holding the transaction.

  2. Louis, if you let the table stay in tempdb for the duration of the procedure and if this procedure takes some considerable time to run to completion, all the data in the table will be stored in tempdb and taking space for no reason. Easy as that. Dropping it as soon as possible at least releases space. This probably doesn’t matter if the proc runs within seconds or less but might be a problem otherwise.

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |