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!