ZZartin wrote: Grant Fritchey wrote:
Take a look at the execution plan. It's going to be all one allocation.
UNION ALL isn't a bad performer in general. In fact, usually, you get better performance with UNION ALL than with UNION because the latter requires aggregation to ensure unique values. However, assuming extremely complex queries on multiple sides in UNION ALL, the optimizer is more likely to timeout. That may be why you get better performance through temp tables. Also, temp tables have statistics which may be assisting a UNION query (doubt they do much in UNION ALL).
One thing to keep in mind repetitive inserts into a temp might not be 100% the equivalent of a UNION(ALL) statement. The UNION will run as a single statement which should maintain locks and what not the entire query. Multiple inserts will not.
Assuming you're in normal READ COMMITTED, the query should only take shared locks on each row and release them after that row is read. Thus, it won't keep any locks for the entire query. If SQL chooses to do some of the SELECTs in the UNION ALL in parallel, then of course those locks would be concurrent.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!