Can this plan be optimized

Pearljammer1 2014-11-17 12:57:33

this is taking nearly 3 minutes to run – can anybody show me where i need to be looking to optimze this pan ?

Dan Holmes 2014-11-17 13:22:55
The estimates vs. actuals go way off at the index seek on WORKPATN. Have you tried to force a HASH JOIN to see if that helps? Can you apply any more predicates or other insights to the query that you know to be true but the optimizer doesn't?

Can you stage some of this in a temp table and join back?

just some first thoughts

Aaron Bertrand 2014-11-17 14:26:12
Also check if stats on that table are horribly out of date.
SQLkiwi 2014-11-18 02:18:05
The area of the plan that jumps out at me is:

Plan fragment

The merge join is an inefficient many-to-many operation, which involves a worktable needed to rewind duplicates. This is followed by an expression computation (Compute Scalar) and a large aggregate (removing duplicates).

You need to focus on the reason for the DISTINCT over the multi-join in the table expression labelled T. The precise solution depends on the logic of the query, and the design of the database. Sometimes, it is possible to utilize computed columns, an additional PK-FK relationship, or to use an APPLY to better express the logic. Which ever way you choose to refactor, the goal is to eliminate the many-to-many join.