I'm a relative newcomer to SQL so apologies if this is stupid or obvious. I'm trying to write a query but am a bit stumped about the execution performance blowing up at what seems to me a pretty innocuous point.
Rather than dump the script code out I'll lay it out in generics; I'm happy to post the exact script but I suspect it's a little long and won't really add anything to my question:
The tenth CTE is doing a LEFT JOIN between a preceding CTE with c. 1800 rows and 2 columns and another preceding CTE with c. 1500 rows and 3 columns. These two CTEs take about 25 secs and 45 secs to calculate respectively, the bulk of which time is inserting into the TVP each time. However when I then come to LEFT JOIN these two performance always blows up. I've cancelled the query each time it got over a couple of minutes before but I've let it run this time whilst I look on the internet. The query is still executing at 54 minutes and counting!
So to my question: have you ever heard of this sort of thing? If so what could be causing it? How do I debug? And the million dollar question, how do I fix it?
Any replies gratefully received.
N.B. I'm running an actual execution plan on the query but can't see anything yet as the query hasn't finished executing yet. I'm SQL Server 2008 if that makes any difference.
XML Plan attached: XMLPlan.zip
You can post here at least Estimated execution plan which you can receive even without executing the query as @Kev Riley mentioned
The issue with TVP and table variables in general is, that there are no statistics generated for them in SQL server and as a result, query optimizer always evaluates the table variables as if they have single record.
So if you have for example a few thousands of records in the table variable and joining it with multi-bilion table, it can be a real performance killer if SQL server chooses wrong query plan because of missing statistics.
In this case it could be better use regular temp table for which statistics are generated and use this in join.
This is one of possible scenarios. But better would be to see a real plan (at least the estimated one).
That is one seriously hairy execution plan. I'm not going to try to pick it all apart, but it looks like you're falling into a classic hole. You're trying to use row by row logic on TSQL, which is a set-based processing system. Sometimes you do need to break data down into steps as you've done, but most of the time, this is an indication that you have some flaws in the data structure which need fixing or that you haven't tried enough using the set-based methods within TSQL.
My suggestion, take a step back and reassess what data you need to get out of this query and then start exploring using a single statement to eliminate it. For example, just that first step after you load the table valued function, deleting data. That means you removed information that you just loaded. You made the assumption that joining to a table would be slower. It might be, but, with propery indexes and good code, joins are probably going to be substantially faster, not slower.
Explore the rest of the code thinking along those lines.
answered Feb 06 '12 at 10:51 AM
Grant Fritchey ♦♦
Oh my GOD. @maccas Seems like while trying to simplify the logic by breaking into pieces (With use of CTEs), you have over-complicated the query. There is redundant work done in the script
could be replaced by one CTE i.e. CurrToPrevMAP1 itself
As far as the rest of the logic is concerned, I think what you need is that PBLeaseID should be NULL in case the there are mutliple residuals i.e. COUNT(LeaseID) > 1. So your FINAL CTES should be (Since, I do not have the sample data and desired output, this is just the hint to make you optimize your query and omit the redundant work)
I hope this will help your cause.
First of all there's no such thing as stupid questions!
Don't think of the 10 CTEs as serial execution, i.e. cte1 runs, processes into cte2 etc.. the CTEs are merely definitions of queries (or result sets, if you want to think of them that way) that are only invoked at execution time. The optimizer can and will flatten the entire query and process it in the best way it can find. Having 10 does seem a little over the norm, and it could be that you have overcomplicated the query so much that the optimizer does not have the time to find the most optimal query plan.
I would prefer to see the code, as while you have done a good job in describing it, nothing beats seeing the code! Plus we would also need DDL for the underlying tables, example data and expected results.
You could also get, and post (as an XML file), the estimated execution plan. (Ctrl-L in SSMS)
answered Feb 05 '12 at 06:28 PM
Kev Riley ♦♦