JOIN HINTS

  • Y.B.

    SSChampion

    Points: 11357

    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

    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

    Great question, thanks.

  • DamianC

    SSCertifiable

    Points: 7767

    Learnt something new, thanks

    - Damian

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

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