JOIN HINTS

Question

JOIN HINTS

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Y.B. SSChampion Points: 11649 More actions · Answer 1

Hugo Kornelis (1/14/2016)
A very good question and I am absolutely happy with it.
One thing that is missing is the explanation of WHY in this specific case enforcing the order impacts the performance. The reason for this is that the Merge Join operator has two modes of operating: one-to-many (which can be used for one-to-one relationships as well), and many-to-many. A many-to-one relationship will normally not occur because during compilation SQL Server will simply switch the order of the two inputs to get a one-to-many.
A Marge Join with one-to-many will use a single pass over both inputs, using basically the balanced line algorithm. For many-to-many, extra complexity has to be added - because operators can not step back, the Merge Join will use a worktable to store copies from the bottom input, and then re-process those copies if the next row from the top input has the same key value, or wipe the worktable and start storing copies again if the kay value in the top input changes. This introduces a huge amount of overhead.
In this case the relationship of the tables as they appear in the join is many-to-one. The plan shown in the question demonstrates that, without hint, the optimizer has swapped the inputs; if you have this as an actual plan instead of a picture you can bring up the properties of the Merge Join operator to verify that the "Many to Many" property is False.
If you then run the code with hint and look at the plan, you will see that the two inputs of the Merge Join are in the same order as in the query (not swapped), and the Merge Join operator will have its "Many to Many" property set to True.

Absolutely appreciate the explanation! I wouldn't have expected the order to matter as in a Nested Loop or Hash Match.

SELECT quote FROM brain WHERE original = 1
0 rows returned

MMartin1 One Orange Chip Points: 27786 More actions · Answer 2

Koen Verbeeck (1/15/2016)
MMartin1 (1/14/2016)
Great question. It reminds me of the merge join transform in SSIS, the inputs have to be ordered in like fashion.
It's not exactly the same. In SSIS, the data itself has to be sorted, but you can swap the inputs of you like.

I think we are saying the same thing here. Ordered , Sorted.

----------------------------------------------------