BUG: Optimizer Uses Scan with Multiple OR Clauses on Composite Index (223423)


The information in this article applies to:

  • Microsoft SQL Server 7.0
  • Microsoft SQL Server 2000 (all editions)
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 <columns> 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.
Modification Type:MajorLast Reviewed:10/17/2003
Keywords:kbBug kbpending KB223423
©2003 Microsoft Corporation. All rights reserved.