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"

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.


Related Articles

Using Included Columns in SQL Server 2005 (14 January 2008)

SQL Server Indexes: The Basics (26 November 2007)

Using Indexed Computed Columns to Improve Performance (16 January 2007)

The Cost of GUIDs as Primary Keys (8 January 2005)

Microsoft SQL Server 2000 Index Defragmentation Best Practices (2 May 2004)

MSDN: Improving Performance with SQL Server 2000 Indexed Views (5 October 2000)

MSDN: Index Tuning Wizard for Microsoft SQL Server 2000 (4 October 2000)

Using a Covered Index (7 September 2000)

Other Recent Forum Posts

I have a error called "Could not find stored procedure 'usp_binarytreecustomerlist'." (2h)

Cannot Show requested dialogue - Timeout Expired. The Timeout priod elapsed prior to completion of the operation or the server is not responding (7h)

Basic SQL query? (20h)

T-sql - we created Message from app1 and trying to disable from app2 (1d)

SQL select Top 10 records for unique combination of two columns (1d)

SSRS Report Sorting with Grouping Issue (3d)

ORA-01476: divisor is equal to zero (3d)

Create new columns based on min and max values of a record with multiple rows (3d)

- Advertisement -