FIX: Slow Query Performance Using Correlated Subquery with "Or Exists" Clause

• Microsoft SQL Server version 7.0

SYMPTOMS

A query that contains a correlated subquery with an OR EXISTS clause may perform slowly.

WORKAROUND

You can rewrite the query and include a redundant clause in the subquery as shown in the following code example:

select A.col1 from TableA A, TableB B 
where 
A.col1 = B.col1
and B.col2 = 'X'
and  
(A.col2 = 'Y' 
  or  exists (select * from tableC C
                 where A.col3 = C.col3
		 -- Add redundant clause from outer query
                 and B.col2 = 'X'
                 )
) 

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 7.0. This problem has been corrected in U.S. Service Pack 1 for Microsoft SQL Server version 7.0. For information about downloading and installing the latest SQL Server Service Pack, see http://support.microsoft.com/support/sql/.

For more information, contact your primary support provider.

MORE INFORMATION

The following sample code demonstrates the problem:

select A.col1 from TableA A, TableB B 
where 
A.col1 = B.col1
and B.col2 = 'X'
and  
(A.col2 = 'Y' 
  or  exists (select * from tableC C
                 where A.col3 = C.col3
		 --and B.col2 = 'X'
                 )
) 

Additional query words:

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