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

ID: Q223031


The information in this article applies to:

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:

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:


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