PRB: Reformatting Used in Stored Procedure with Temporary Table

ID: Q189394


The information in this article applies to:
  • 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
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.