This article was previously published under Q244230
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 55001 (SQLBUG_70)
A query that contains a correlated subquery with an OR EXISTS clause may perform slowly.
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.col1and 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' ))
Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 1 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
232570INF: How to Obtain Service Pack 1 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0
For more information, contact your primary support provider.
The following sample code demonstrates the problem:
select A.col1 from TableA A, TableB B where A.col1 = B.col1and B.col2 = 'X'and (A.col2 = 'Y' or exists (select * from tableC C where A.col3 = C.col3 --and B.col2 = 'X' ))
The SHOWPLAN output for this query shows the optimizer is picking a hash match join strategy.