Microsoft KB Archive/189394

= 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

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.