PRB: Reformatting Used in Stored Procedure with Temporary Table (189394)
The information in this article applies to:
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.
| Modification Type: | Major | Last Reviewed: | 10/3/2003 |
|---|
| Keywords: | kbprb KB189394 |
|---|
|