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

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.

MORE INFORMATION

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