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

ID: Q223423

The information in this article applies to:

Microsoft SQL Server version 7.0

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 SQLPrepare() 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 SQLPrepare() or SQLExecDirect() with a parameterized query using the SQL Server ODBC driver version 3.7, use the odbccmpt utility to enable the 6.5 ODBC compatibility option and disable the "Generate stored procedures for prepared statements" option. For more information on the odbccmpt utility, please see the "odbccmpt Utility" topic in SQL Server 7.0 Books Online.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 7.0.

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 SQLPrepare() 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 : kbSQLServ700 kbSQLServ700bug
Version : winnt:7.0
Platform : winnt
Issue type : kbbug