Any suggestion how to reduce the Nested Loop performance?

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

Plan.pesession (12.0 kB)
avatar image By RobertOD 16 asked Oct 13, 2014 at 11:14 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

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:

Predicate

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.

avatar image By SQLkiwi ♦ 6.6k answered Oct 14, 2014 at 09:14 AM
more ▼
(comments are locked)
avatar image RobertOD Oct 14, 2014 at 02:04 PM

Thanks. I figured out that the developer should have used an Exists clause. This radically improved query plan and prevented the Lazy Table Spool.

10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x641
x166
x117
x109

asked: Oct 13, 2014 at 11:14 PM

Seen: 136 times

Last Updated: Oct 14, 2014 at 02:06 PM