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

• Microsoft SQL Server version 7.0

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.

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 version 7.0.

Additional query words: suboptimal

Keywords          : kbbug7.00 
Version           : winnt:7.0
Platform          : winnt 
Issue type        : kbbug 

Last Reviewed: March 20, 1999