Why nested loop instead of more efficient merge join?

I migrated this join from SQL-Server 2008R2 to 2014SP2 with compatibility level 120. On the old server it used a merge join (266 reads), on the new one a nested loop (68632 reads). The execution time is the same, no matter if i use merge join or nested loop. How can i optimize this query beside using query hints to force it to use a merge join? I already tried providing a new index perfectly mathing the needs of object2 but no use.

avatar image By Betrando 16 asked Jan 30, 2017 at 03:28 PM
more ▼
(comments are locked)
avatar image Matak Jan 30, 2017 at 11:12 PM

Your statistics are totally out of whack. Try updating them and rerunning.

avatar image Aaron Bertrand ♦ Feb 03, 2017 at 05:50 PM

If the execution time is the same, why do you need to force a merge join, and why do you think that is more efficient? Using what metric?

10|10000 characters needed characters left

1 answer: sort voted first

I'm curious about the plan that uses the Merge Join - I wonder if there's more to it.

For example, as a Merge Join needs ordered data, they can sometimes push data through expensive Sort operations.

A Merge Join can run in 1-to-many or many-to-many mode, and the latter is way more expensive. Check the Residual Predicate property of the Merge Join too, to see if it's having to do more work than you'd expect.

As for your Nested Loop plan that you've attached, the inside Seek runs 34182 times and has 68632 reads. That's two reads per execution. This tells me that this index is only two levels deep, therefore there can't be many pages in the whole thing. Remember that when pages are already in RAM, reads are very cheap. I'm guessing there might be only dozens of pages, and that the true I/O effort here is a lot cheaper than your 68632 reads would suggest.

There was a time when I tuned a query from several minutes down to a few seconds, but in doing so, the reads went up from about 300 thousand to over 9 million. Based on the reads, it should've been 30x slower, but it was actually more than 30x faster.

avatar image By Rob Farley 196 answered Feb 05, 2017 at 07:53 AM
more ▼
(comments are locked)
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



asked: Jan 30, 2017 at 03:28 PM

Seen: 332 times

Last Updated: Feb 05, 2017 at 07:53 AM