Tech Stuff

6. Optimizing Performance / Indexes on Temp Tables


"The general rule is to avoid using temp tables, usually in favor of derived tables or table variables, but there are times when it seems that nothing else will do the job. Or, maybe we just inherit complex code that already makes extensive use of temp tables. Frequently, query performance against large temp tables can benefit from adding a few well-chosen indexes"

Example 1:

CREATE TABLE #temp_employee_v1
(emp_id int not null,
lname varchar (30) not null,
fname varchar (30) not null,
city varchar (20) not null,
state char (2) not null )

CREATE UNIQUE CLUSTERED INDEX IX_1 on #temp_employee_v1 (lname, fname, emp_id)
CREATE INDEX IX_2 on #temp_employee_v1 (state, city)

Sometimes, though, it seems the optimizer is intent on ignoring the very performance-boosting indexes that we’ve just created. This usually happens because the access plans for the temp tables have been generated before the indexes ever existed.
Fortunately, we can use table-level constraints to get around this problem. Since the indexes to support UNIQUE and PRIMARY KEY constraints are defined at the same time as the temp table, the optimizer will always be able to use these indexes. The only issue is that both these types of constraints require unique values, and we may want to create indexes on non-unique columns. This is usually pretty easy to handle. In the best case, our temp table will already have a column that contains unique values, e.g. emp_id from Example 1. When that's the case, you can simply append the unique column as the last column in your constraint definition to meet the uniqueness requirement (see Example 2).


Example 2:
CREATE TABLE #temp_employee_v2
(emp_id int not null,
lname varchar (30) not null,
fname varchar (30) not null,
city varchar (20) not null,
state char (2) not null,
PRIMARY KEY (lname, fname, emp_id),
UNIQUE (state, city, emp_id) )

Unfortunately, there are cases when our temp tables don't come supplied with a unique-valued column. In that case, we can add an identity column to meet our requirements, e.g. the rowID column in Example 3. Once armed with a unique-valued column, we can use the same technique we used in the previous example.


Example 3:
CREATE TABLE #temp_employee_v3
(rowID int not null identity(1,1),
lname varchar (30) not null,
fname varchar (30) not null,
city varchar (20) not null,
state char (2) not null,
PRIMARY KEY (lname, fname, rowID),
UNIQUE (state, city, rowID) )

The main thing to remember when using this technique is to not assign names for your constraints. Since constraint names have to be unique across table name for a given user, naming a constraint can cause problems if more than one copy of the script or stored procedure is running simultaneously. If you let SQL Server generate the name, this shouldn't be a problem.

In general, is there a performance difference between using table variables and using temporary tables in SQL Server 2000?
There can be a big performance difference between using table variables and temporary tables. In most cases, temporary tables are faster than table variables. I took the following tip from the private SQL Server MVP newsgroup and received permission from Microsoft to share it with you. One MVP noticed that although queries using table variables didn't generate parallel query plans on a large SMP box, similar queries using temporary tables (local or global) and running under the same circumstances did generate parallel plans.

A senior member of the SQL Server development team told me that table variables use internal metadata in a way that prevents the engine from using a table variable within a parallel query. He also said that SQL Server maintains statistics for queries that use temporary tables but not for queries that use table variables. Without statistics, SQL Server might choose a poor processing plan for a query that contains a table variable. The development team member added that you should limit your use of SQL Server 2000 table variables to reasonably small queries and data sets and use temporary tables for larger data sets.