September 13, 2019 at 9:30 pm
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.
September 14, 2019 at 10:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
September 16, 2019 at 2:55 am
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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply