September 3, 2009 at 7:44 am
I am having a performance issue with a query that contains UNION ALL. If I run the queries separately (view vwA and vwB, for example), then their execution time is subsecond (A returns 50 rows, B returns 15 rows).
But, when I UNION ALL them together (select * from vwA UNION ALL select * from vwB), then the query returns the expected 65 rows, but now takes 16 seconds to run.
What would explain the resulting performance degredation?
Brian
September 3, 2009 at 8:27 am
hard to tell, we need the qry plan. The engine should run one then run the 2 sec one then add them together.
September 3, 2009 at 10:14 am
Without being able to see the query plan - can't say for certain, but I have in hte past seen similar types of degradation (or even worse) using derived tables where once the derived tables are linked together in one query it just causes the optimiser to barf and chose a plan that sucks.
Such circumstances are often a case for Cunning Use of Temp Tables - or a redesign as it usually only seems to occur when in the company of bad design. Temp table solution is quicker, less likely to cause greying/hair loss and easier to sell to management!
Try generating a temp table to hold the data - run one after the other into the temp table and see if you're back subsecond. If so - it's probably that scenario.
September 3, 2009 at 10:25 am
465789psw (9/3/2009)
hard to tell, we need the qry plan. The engine should run one then run the 2 sec one then add them together.
No. When you combine views (or a view with other objects) the optimiser resolves the query plan using the component parts of the view(s) - effectively ignoring the view itself and building up from the base tables.
September 3, 2009 at 11:15 am
Brian Cromwell (9/3/2009)
I am having a performance issue with a query that contains UNION ALL. If I run the queries separately (view vwA and vwB, for example), then their execution time is subsecond (A returns 50 rows, B returns 15 rows).But, when I UNION ALL them together (select * from vwA UNION ALL select * from vwB), then the query returns the expected 65 rows, but now takes 16 seconds to run.
What would explain the resulting performance degredation?
Brian
I suspect vwA and vwB are views with at least one common table. This could result in parallel queries blocking each other. Ideally views should not be used and the query written in such a way so as to avoid blocking. The simple way to check this idea is to stop parallel queries by using OPTION (MAXDOP 1):
SELECT *
FROM vwA
UNION ALL
SELECT *
FROM vwB
OPTION (MAXDOP 1)
Of course the OP could always provide the query plan!
September 3, 2009 at 12:00 pm
Combining views is not dissimiler to joining views or nesting views. It all leads to poor choices by the optimizer due to the complexity of the plan when the optimizer unpacks the views to look at all their component parts and then tries to put these parts together into a single execution plan.
This is basically a "doctor, doctor, it hurts when I do this" situation.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 3, 2009 at 4:26 pm
Thank you for all of the responses. Looking at the graphical version of the execution plan for the UNION ALL query versus running the queries separately, it is easy to see that the combined query does not look like the separate queries joined together.
It makes sense now that the optimizer would try to do what it thinks is best for me, but my first thought (which was wrong) was that a UNION ALL and running queries separately would always produce somewhat similar results.
I will now go through the process of tuning the Union'ed query.
Also, thanks for the link on how to post performance-related queries. As a new poster, I appreciate the pointer.
Thank you,
Brian
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply