Microsoft KB Archive/223031

= BUG: Optimizer Picks Table Scan for IF EXISTS Subquery with BETWEEN and Local Variable =

Article ID: 223031

Article Last Modified on 10/16/2002

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q223031



BUG #: 54815 (SQLBUG_70)



SYMPTOMS
A specific type of query may, under narrow conditions, cause the optimizer to pick an access plan that is not optimal. The symptoms of this problem are excessive I/O (if data is not cached) or excessive CPU usage (if data is cached). For this problem to occur, the following conditions must be present:
 * You have an IF EXISTS subquery with a local variable in the WHERE clause.

-and-
 * There are column statistics on a second poor-cardinality column in the WHERE clause.

The following is an example of a query that demonstrates this problem:

DECLARE @nextid int SELECT @nextid = 6011050 IF EXISTS (SELECT col1 FROM table WHERE col2 = 1                  AND col1 BETWEEN @nextid AND 6011051) print 'hello'



WORKAROUND
To work around this problem, try one of the following:  Drop column statistics on the indicated column. It may be necessary to disable auto create statistics in the database to avoid re-creating column statistics on that column. Rewrite the query to use a constant rather than a variable in the WHERE clause.  Rewrite query to not use IF EXISTS. For example, instead of:

IF EXISTS( SELECT * FROM table WHERE column BETWEEN @v1 AND @v2 )

Write the query as:

SELECT @r = (SELECT COUNT(*) FROM table WHERE column BETWEEN @v1 AND @v2) IF @r > 0 ....and so on. 



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

Additional query words: suboptimal

Keywords: kbbug kbpending KB223031

-

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

© Microsoft Corporation. All rights reserved.