Simplify a complex query II

richard101 2014-06-09 10:55:13

apologies for 3x posts on this subject 🙁
Mart 2014-06-09 11:03:52
Ok, thanks richard.

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 🙂

richard101 2014-06-09 11:10:56
Thanks Mart

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?

Mart 2014-06-09 12:55:43
Hey Richard

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?

richard101 2014-06-09 13:13:00
yes, no one here can decifer it. Currently I'm steping through it pasting sub-selects to temp tables at the top – not sure if the result will be clearer 🙂

Shame we cannot reverse engineer a query from the data and the output 🙁 or drag the executable code from the plan-cache :((

Mart 2014-06-09 16:05:28
You can get the code from the plan cache kind of like this but it's the code you pass so will make no difference unfortunately.

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);
Mart 2014-06-09 16:07:15
I think what you're doing will allow you to get all the component parts separated then you can take a look at each bit in isolation and work out what's needed, what's not and what can be re-written.
Mart 2014-06-10 07:38:33
How you getting on Richard. Do you need any help with any of the re-writes or are you ok now?
richard101 2014-06-10 08:34:25
Hi Mart, well, its now totally 'un-nested'. However, I haven't found any code to comment out that doesn't break it. Surely it can be simplified somehow?
richard101 2014-06-10 10:51:57
Hi Mart, ok Ive been through the lot, and deleted a few rows (by trial-and-error), and added an ORDER BY to stabalize my results.
June 9, 2014
No Comments