I have been running this query for about an hour and it did not finish. I have looked at the query plan and it did not show anything wrong with it.
Please I really do need help optimizing the queries
By Deamon 0 asked Apr 07 at 06:50 PM
Well, I'm not so sure... if you hover over the tooltip for the top-left SELECT operator, you can spot multiple problems:
Nothing in the graphical plan really stands out because the work is spread out across so many operators, even we have a hard time making the most painful part obvious:
If you go to the Top Operations tab, and sort by estimated I/O cost, you'll see that the largest cost is the scan on Object13.Index3. Because you've anonymized the plan, it's impossible to tell what the predicate is (I couldn't find Object13 in the query text), but perhaps there is a way to change the predicate (or the index) such that a scan isn't required. The next most expensive is Object1.Index11 (also not found in the statement text).
But let's be realistic. Even if you do improve these few expensive operators, this is still a whopper of a query for the optimizer to try and wrangle. Instead of having all these CTEs, you might want to consider materializing some of those results into intermediate temp tables, which (a) give the optimizer more flexibility, (b) provide for the ability to create additional indexes and localized statistics, and (c) prevent CTEs that are referenced more than once from being materialized more than once.
By Aaron Bertrand ♦ 1.7k answered Apr 11 at 08:26 PM