Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

UNION ALL Performance Expand / Collapse
Author
Message
Posted Thursday, September 03, 2009 7:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 06, 2012 11:50 AM
Points: 2, 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
Post #782168
Posted Thursday, September 03, 2009 8:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 12, 2010 8:40 AM
Points: 190, 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.




Post #782225
Posted Thursday, September 03, 2009 10:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 29, 2010 5:23 AM
Points: 194, Visits: 2,357
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.
Post #782349
Posted Thursday, September 03, 2009 10:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 29, 2010 5:23 AM
Points: 194, Visits: 2,357
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.
Post #782355
Posted Thursday, September 03, 2009 11:15 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 6:54 AM
Points: 834, Visits: 5,322
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!
Post #782399
Posted Thursday, September 03, 2009 12:00 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:36 AM
Points: 14,794, Visits: 27,269
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #782452
Posted Thursday, September 03, 2009 4:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 06, 2012 11:50 AM
Points: 2, 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

Post #782614
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse