How much memory is allocated when you UNION ALL?

  • 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

  • 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.

  • 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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • 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.

  • 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 .

  • 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.

  • 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!

  • Scott-144766 wrote:

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • allinadazework wrote:

    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