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

Consider a simple query of the following form:SELECT o.IDFROM dbo.OUTER oWHERE NOT EXISTS(SELECT 1FROM dbo.INNER iWHERE o.ID = i.ID);Sometimes the query optimizer will implement the join as a left anti semi nested loop joinwith a TOP operator applied to the scan on the dbo.INNER table. The row goal introducedby 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 willalways 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 thatdo not have a matching row in the dbo.INNER table.

Under Review

Query Processing and Intelligent DatabaseBugs

Company Response

Microsoft

Company Response

Up: 15

0 Comments

You must to comment