PRB: Reformatting Used in Stored Procedure with Temporary Table

• Microsoft SQL Server version 6.5

SYMPTOMS

The SQL Server optimizer may choose to use a reformatting strategy for a join when in fact it is faster to use a nonclustered index. This can occur if all of the following conditions are true:

• The join is inside of a stored procedure.



• The join is between a temporary table and a large table.



• The temporary table is created in the stored procedure.



• The large table has a nonclustered index for the join condition.



• The data in the temporary table is small (less than 10 rows).

WORKAROUND

To work around this problem, try any of the following:

• Create the temporary table outside of the stored procedure that the join is in.



• Rearrange the indexes on the large table so that the index that supports the join condition is a clustered index.



• Create a cursor on the results of the join.



• Run the join query in a batch instead of a stored procedure.

MORE INFORMATION

The SQL Server optimizer chooses the nonclustered index for the join when it is run from a batch because the number of rows in the temporary table is known when the join query is optimized.

Additional query words: performance slow non clustered non-clustered

Keywords          : SSrvStProc SSrvTran_SQL 
Version           : WINNT:6.5
Platform          : winnt 
Issue type        : kbprb 

Last Reviewed: April 19, 1999