SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


UNION ALL Performance


UNION ALL Performance

Author
Message
Brian Cromwell
Brian Cromwell
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 40
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
465789psw
465789psw
Old Hand
Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)

Group: General Forum Members
Points: 346 Visits: 472
hard to tell, we need the qry plan. The engine should run one then run the 2 sec one then add them together.



Andrew Gothard-467944
Andrew Gothard-467944
Old Hand
Old Hand (368 reputation)Old Hand (368 reputation)Old Hand (368 reputation)Old Hand (368 reputation)Old Hand (368 reputation)Old Hand (368 reputation)Old Hand (368 reputation)Old Hand (368 reputation)

Group: General Forum Members
Points: 368 Visits: 2357
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.
Andrew Gothard-467944
Andrew Gothard-467944
Old Hand
Old Hand (368 reputation)Old Hand (368 reputation)Old Hand (368 reputation)Old Hand (368 reputation)Old Hand (368 reputation)Old Hand (368 reputation)Old Hand (368 reputation)Old Hand (368 reputation)

Group: General Forum Members
Points: 368 Visits: 2357
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.
Ken McKelvey
Ken McKelvey
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1721 Visits: 7917
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!
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41739 Visits: 32666
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Brian Cromwell
Brian Cromwell
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 40
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search