Microsoft KB Archive/288319

= PRB: Suboptimal Query Plan Executed When Same Join Predicate Appears on Both Sides of an OR Clause =

Article ID: 288319

Article Last Modified on 3/17/2001

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q288319



SYMPTOMS
When you use a non-ANSI syntax for an inner join, if the same join predicate appears on both sides of an OR clause, a suboptimal execution plan may be used, which may result in substantial performance degradation when a large amount of data is processed.



RESOLUTION
To work around this problem, do one of the following:
 * Move the join predicate outside of the OR clause.

-or-
 * Rewrite the query using ANSI join syntax so that the join predicate appears only once.



Steps to Reproduce Behavior
The following script demonstrates the difference in the execution plan for a query written in two different ways: USE pubs GO

SET STATISTICS PROFILE ON GO

-- A suboptimal plan will be used for this query. SELECT COUNT(*) FROM authors a, titleauthor ta WHERE ((a.au_id = ta.au_id AND a.au_fname = 'Ann') OR (a.au_id = ta.au_id AND a.au_fname = 'Stearns')) GO

-- A good plan will be used for these two queries. SELECT COUNT(*) FROM authors a, titleauthor ta WHERE a.au_id = ta.au_id AND (( a.au_fname = 'Ann') OR (a.au_fname = 'Stearns')) GO

SELECT COUNT(*) FROM authors a JOIN titleauthor ta ON a.au_id = ta.au_id WHERE ( a.au_fname = 'Ann') OR (a.au_fname = 'Stearns') GO

SET STATISTICS PROFILE OFF GO

Keywords: kbprb KB288319

-

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

© Microsoft Corporation. All rights reserved.