Slow Query Hash Match

tharmen 2016-12-04 04:20:47

We are runnig one query which takes one jour to finish. How can i make it faster?
I have attached the execution plan below.
Any idea?

Matak 2016-12-11 22:34:09
Your stats seem way out. Do you do any maintenance around them ? nolock hints are generally not a good idea. Any reason why you are using them (they dont make queries go faster). For a small improvement you can tweak your indexes to remove the key lookups.
Aaron Bertrand 2017-01-08 17:09:32
Multiple DISTINCTs are definitely a warning sign for me, as is the hash match which results from a self-join. What are the actual requirements for this query? I also agree with Matak about the key lookups (it should be easy to add the missing columns to those indexes to eliminate those) and stats (SQL Server seems to have absolutely no clue how many rows might be in the Trend table).