Microsoft KB Archive/223423

= BUG: Optimizer Uses Scan with Multiple OR Clauses on Composite Index =

Article ID: 223423

Article Last Modified on 10/17/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q223423



BUG #: 54452 (SQLBUG_70)



SYMPTOMS
Queries with multiple OR clauses, based on the fields in a composite index, may display slow execution times as the optimizer does not use the index for the query, but instead uses a scan. This only happens when the query is executed from within a stored procedure, or executed through an ODBC application.



WORKAROUND
Here are five options you may use to avoid this problem:


 * Switching the index from a clustered index to a non-clustered index may cause the optimizer to use the index in the query plan.

-or-
 * Rewrite the query to utilize a UNION clause to combine the results returned from the multiple OR clauses.

-or-
 * If the query is contained within a stored procedure, use an index hint to force the appropriate index.

-or-
 * If the query is not within a stored procedure, but is being executed from an ODBC application through the SQLPrepare function with the SQL Server ODBC driver version 3.6 or earlier, disable the "Generate Stored Procedures for Prepared Statements" option.

-or-
 * If the query is not within a stored procedure, but is being executed from an ODBC application through the SQLPrepare or SQLExecDirect function with a parameterized query using the SQL Server ODBC driver version 3.7, use the odbccmpt utility to enable the SQL Server 6.5 ODBC compatibility option and disable the "Generate Stored Procedures for Prepared Statements" option. For more information on the odbccmpt utility, see the "odbccmpt Utility" topic in the SQL Server 7.0 Books Online.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.



MORE INFORMATION
Microsoft Access applications with linked tables may experience this problem as Microsoft Access fetches 10 rows at a time based on the primary key. For example, if the primary key of a table is on columns a and b, you will see the following type of query sent to SQL Server when opening the linked table: select from table1 where a = 1 and b =1 OR a = 2 and b = 2 OR a = 3 and b = 3 OR .... OR a = 10 and b = 10 If this is sent to SQL Server as a straight SQL query, the optimizer chooses to use the index and performance is as expected. However, since Microsoft Access uses the SQLPrepare function for the query, if the "Generate Stored Procedures for Prepared Statements" option is set ON for the 3.6 and earlier SQL Server ODBC driver, the query executes as a temporary stored procedure. When using the 3.7 driver the prepare\execute model is used, which also does not use the index.

Additional query words: slow poor performance hang

Keywords: kbbug kbpending KB223423

-

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

© Microsoft Corporation. All rights reserved.