Any suggestion how to reduce the Nested Loop performance?

RobertOD 2014-10-13 23:14:52

I am not sure why the Index Seek returning 9,788 rows becomes 127,929.160 in the Lzay Table Spool.

SQLkiwi 2014-10-14 09:14:57
The section of the plan in question is:

Spool Fragment

The 9,788 rows read from the Index Seek are replayed by the spool once for each row arriving at the Nested Loops join's top input. 9788 * 13,070 = 127,929,160.

The Nested Loops join applies the following predicate on each replay:


A better strategy would be to perform a correlated seek, with no join residual. You should check the indexes available on the table underlying this view to make sure the correlated seek strategy is viable.

RobertOD 2014-10-14 14:04:09
Thanks. I figured out that the developer should have used an Exists clause. This radically improved query plan and prevented the Lazy Table Spool.