PRB: Reformatting Used in Stored Procedure with Temporary Table
Article ID: 189394
Article Last Modified on 10/3/2003
APPLIES TO
- Microsoft SQL Server 6.5 Standard Edition
This article was previously published under Q189394
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: kbprb KB189394