March 31, 2022 at 2:21 pm
If I have a query with multiple UNION ALLs, will the optimizer try and grab enough memory to run all the individual queues in one, or will it make individual allocations for the separate queries? Google has not been my friend here.
Thanks
Scott
--
Scott
March 31, 2022 at 8:24 pm
I don't know the specific answer to your question but I have generally found it better to create a temp table or table variable (depending on size) and insert each of the SELECTs from my UNION. Then select the data from the temp table at the end. Not sure why, but certainly with using UNION ALL I have had issues sometimes with the query optimiser and performance.
April 1, 2022 at 12:02 pm
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).
"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
April 1, 2022 at 3:39 pm
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.
April 2, 2022 at 9:51 am
All members of the union shares the common memory. In above case total of 4 bytes gets allocated for u because in 4 bytes(MAX memory needed) you can store both i and ch .
June 23, 2022 at 1:00 pm
The total memory required to store a structure variable is equal to the sum of size of all the members. In above case 7 bytes (2+1+4) will be required to store structure variable s1. In union, the total memory space allocated is equal to the member with largest size. All other members share the same memory space.
June 23, 2022 at 4:42 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 24, 2022 at 1:21 am
If I have a query with multiple UNION ALLs, will the optimizer try and grab enough memory to run all the individual queues in one, or will it make individual allocations for the separate queries? Google has not been my friend here.
Thanks
Scott
I guess my question would be, why not try it and find out by looking in the Execution Plan?
"One good test is worth a thousand expert opinions".
--Jeff Moden
Change is inevitable... Change for the better is not.
June 24, 2022 at 9:37 am
I don't know the specific answer to your question but I have generally found it better to create a temp table or table variable (depending on size) and insert each of the SELECTs from my UNION. Then select the data from the temp table at the end. Not sure why, but certainly with using UNION ALL I have had issues sometimes with the query optimiser and performance.
Yes, I think the optimiser will try to run all the queries at the same time so I've found splitting the query into multiple queries often helps particularly when the query is large so overloads the database.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply