Use More Than Density to Cost a Scan on the Inner Side of a Nested Loop with TOP - by Joe Obbish

Status : 

 


15
0
Sign in
to vote
ID 3142804 Comments
Status Active Workarounds
Type Bug Repros 1
Opened 10/19/2017 7:03:56 AM
Access Restriction Public

Description

Consider a simple query of the following form:

SELECT o.ID
FROM dbo.OUTER o
WHERE NOT EXISTS
(
	SELECT 1
	FROM dbo.INNER i
	WHERE o.ID = i.ID
);

Sometimes the query optimizer will implement the join as a left anti semi nested loop join
with a TOP operator applied to the scan on the dbo.INNER table. The row goal introduced
by TOP aggressively reduces the cost of the repeated scan on the dbo.INNER table.
The density of the table is used in the calculation to reduce the cost of the scan.
Using the density means that the query optimizer assumes that a matching row will
always be found and that the scan can always terminate early.
This can lead to performance issues when there are rows in the dbo.OUTER table that
do not have a matching row in the dbo.INNER table.
Sign in to post a comment.