Hash Match (Inner Join) vs Loop Join

  • jay_in_toronto

    SSC Rookie

    Points: 32

    Hello,

    I have an encrypted view that, when I do a select on, takes over two minutes to return data.  I've discovered that by adding "OPTION (Loop Join)" the same query runs in only a few seconds.  This confused me somewhat because some of the research I've done online indicates Hash Matches are often better than Loop Joins.

    What could explain this behavior?  Is there any way to force the execution plan to use Loop Joins instead of Hash Matches without specifying the query hint?

    Thanks,

    Jay.

  • Site Owners

    SSC Guru

    Points: 80380

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • Leo.Miller

    SSChampion

    Points: 12824

    As with a lot of things,  "Hash Matches are often better than Loop Joins" has caveats. For small tables a Loop Join may be better, particularly if the inner loop is very small (only a few rows). It would be handy if you had some of the logic or the two execution plans to do comparisons. Given the view is encrypted, I take it this would be difficult to arrange. Could you get a copy of the T-SQL Code and generate the execution plans?

    You could try putting in a query plan guide that automatically adds the option, but without some more information around this it's difficult to give more feedback.

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

Viewing 3 posts - 1 through 3 (of 3 total)

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