2

At the moment I have a view that is running very slow. It takes about 3 minutes to execute. This suddenly happened.

To debug what was going on I used an old dataset and compared both queries. On the old dataset the view returns the data within a second.

I noticed one difference: the current dataset runs on "nested loops" while my old dataset runs with a Hash Match.

You can see that in the picture:

Example dataset 1 (current) vs  2 (old)

I'm aware of the fact that you simply cannot "force" it to use Hash Match as I saw in other topics, yet I'm having a really hard time finding out why its going for a nested loop.

Is this because something in my dataset is not unique?

How can I fix my current dataset to start using this Hash Match again?

4

If you are going to this you should at least use the OPTION ( HASH JOIN ) syntax instead. Using the LEFT HASH JOIN syntax implicitly enforces the join order.

This is not desirable for hash joins where normally you would expect the smaller of the two tables being joined to be the build input, the top-most table in the execution plan. Using the option syntax allows the optimizer to decide at run-time which is the best join order. See these two articles for further reading:

http://blogs.msdn.com/b/craigfr/archive/2006/08/10/687630.aspx

https://msdn.microsoft.com/en-GB/library/ms173815.aspx

  • The caveat to this method is that if you had multiple joins and used OPTION (HASH JOIN) then this would impact ALL joins, not just the one you perhaps want to target. – Mark Sinkinson Jan 6 '16 at 11:17
1

Ill just answer this so it can be 'closed'.

The solution was from @Mark Sinkinson

I could force the Hash Match via

LEFT OUTER HASH JOIN

While I permanently fixed it by updating my statistics via

UPDATE STATISTICS table_or_indexed_view_name

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.