Post reply

Performance Question on Unions vs multiple inserts in temp table

  • April 6, 2005 at 6:27 pm

    #61120

    I have a stored procedure that I can either use serveral unions and return the data in 1 query or create a temp table and insert each one of the query results and then return the temp table. I always heard unions were slow but during my tests it appears the union method is faster. Just looking for some input.

    Thanks!

    Kurt

  • April 7, 2005 at 1:47 am

    #551053

    It all depends on the type of union.

    If it is a union ALL without an order by, the union will be faster, as the server will not need to use a Worktable. If not, the server will use a Worktable to do the distinct or the order by, and this means it will do more or less the same as you are doing when you use a temp table ( better : use a table variable ).

     

    Bert

  • April 7, 2005 at 1:58 am

    #551056

    I would definately recommend going with the unions. A logical union is not slow in any way, however the implementation requires a distinct operation to remove any duplicates. Like Bert said, if you know that the inputs to the union does not contain duplicates you can help the optimizer by executing a UNION ALL instead.

    --
    Chris Hedgate http://www.hedgate.net/
    Contributor to the Best of SQL Server Central volumes
    Articles: http://www.sqlservercentral.com/columnists/chedgate/

  • April 7, 2005 at 6:52 am

    #551122

    thanks for the replies.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply