JOIN HINTS

• Y.B.

  SSChampion

  Points: 11357

  January 18, 2016 at 7:24 am

  #1852511

  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: 27488

  January 19, 2016 at 11:03 am

  #1852862

  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.

  ----------------------------------------------------
  How to post forum questions to get the best help [/url]

• akljfhnlaflkj

  SSC Guru

  Points: 76202

  February 17, 2016 at 4:52 pm

  #1859433

  Great question, thanks.

• DamianC

  SSCertifiable

  Points: 7767

  November 3, 2016 at 10:19 am

  #1910400

  Learnt something new, thanks

  - Damian