Optimizing Performance / Indexes on Temp Tables

By Guest Authors on 5 January 2004 | Tags: Indexes

This article is from Bill Richmond. Bill writes "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"

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.