Simplify a complex query II
I guess there are a few ways to tackle this, depending on what you are allowed to do. IF you just need to understand it better then I'd begin by looking at each of the sub selects and see how they work/perform on their own.
I'd then begin to join them back together bit by bit to see how things looked then and get an idea of how it all works together.
The first step though for me would be to check when the statistics were last updated as the estimate and actual values are way out and simply updating the stats may give you a decent improvement.
Let me know if I'm on the right tracks to what you're trying to achieve.
Cheers
Mart
P.S. for testing it could be easier to work with if the more complex sub queries are put into temp tables, this may or may not be the way to do it in production but it would probably make it easier to work through π
It may well be worth having a chat with the end users in that case to see what is still needed, you may be able to clean it up by simply having less in it.
Are you just trying to make it easier to read/maintain rather than improve the performance at this stage?
Shame we cannot reverse engineer a query from the data and the output π or drag the executable code from the plan-cache :((
SELECT dest.TEXT
FROM sys.dm_exec_cached_plans decp WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(decp.plan_handle) dest
CROSS APPLY sys.dm_exec_query_plan(decp.plan_handle) AS deqp
WHERE dest.dbid = db_id()
OPTION (MAXDOP 1,RECOMPILE);
I don't think our (local) stats matter as the code is sent to customer to apply to there own local server.
I suspect this query was made by cut-paste old working queries, and therefore most of it is redundant. I wonder if getting the actual run-code would look simpler?