How to optimize query?

mr.adam.zielinski 2017-03-06 11:36:02

I have to optimize query, I'm considering using CTE and maybe updating stastistics (hovewer I try that once without any results)
What should I do first?

Aaron Bertrand 2017-03-07 01:42:38
Well the spools look really bad, as does the use of table variables for this much data, and of course the estimates of 1.3 billion rows from Object50 that turn out to only be 14K rows. Generally, CTEs don't improve performance but rather are used for logical structure / readability. I'd love to take a look at the index analysis to see some better info about statistics and potential index changes, but because you've anonymized the plan, that information is not available. Any chance you could submit a non-anonymized plan? You can e-mail it to me at abertrand@sentryone.com if it really needs to be anonymized.