Alternative option to UNION ALL

  • I am trying to see if i can replace 35 Union ALL's on 35 table in my view. I will try to post the actual code, but i am open for any suggestion which could enhance the performance.

  • You should read this blog posting:

    http://blog.sqlauthority.com/2009/03/11/sql-server-difference-between-union-vs-union-all-optimal-performance-comparison/

    Which contains:

    A UNION statement effectively does a SELECT DISTINCT on the results set. If you know that all the records returned are unique from your union, use UNION ALL instead, it gives faster results.

    The blog post also contains sample code so that you can test for yourself which, in your case, would be the most efficient.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • What I'm about to suggest may or may not help you. Performance is dependent on your schema and data. So test this out for performance before you put it into Production.

    1) Use a temp table and do individual inserts, then SELECT the resultant data from the temp table.

    2) Use CTEs to put the data together.

    3) Look at your 35 UNION ALLs and determine if someone wrote them that way because they were being lazy with WHERE clauses or if you can combine them into one SELECT, using CASE expressions and other such tricks to get the individual columns to say what they should.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Please post the actual code.

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

  • I'd have to see the reason for the Union All statements (evident from the query, most likely) to be more specific, but if it's all coming from one table or the same tables, then it's probably just someone trying to avoid OR statements in a Where clause. That's the most usual reason I see for that. It was a very valid performance-tuning technique in older versions of SQL Server, but not so valid since SQL 2005.

    It can often be avoided and simplified by using a parameterized dynamic query. That's usually faster, too, as well as easier to maintain.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • GSquared (4/23/2012)


    I'd have to see the reason for the Union All statements (evident from the query, most likely) to be more specific, but if it's all coming from one table or the same tables, then it's probably just someone trying to avoid OR statements in a Where clause. That's the most usual reason I see for that. It was a very valid performance-tuning technique in older versions of SQL Server, but not so valid since SQL 2005.

    It can often be avoided and simplified by using a parameterized dynamic query. That's usually faster, too, as well as easier to maintain.

    Union is done across 35 different tables.

  • Why do you want to replace the union all then? For concatenating multiple tables, there isn't usually a faster way.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Not such a great solution but I replaced this with a table.I have a job which will basically populate the table and the stored procedure will just read from the table.

  • sqldba_newbie (4/23/2012)


    GSquared (4/23/2012)


    I'd have to see the reason for the Union All statements (evident from the query, most likely) to be more specific, but if it's all coming from one table or the same tables, then it's probably just someone trying to avoid OR statements in a Where clause. That's the most usual reason I see for that. It was a very valid performance-tuning technique in older versions of SQL Server, but not so valid since SQL 2005.

    It can often be avoided and simplified by using a parameterized dynamic query. That's usually faster, too, as well as easier to maintain.

    Union is done across 35 different tables.

    As far as I know, UNION is the only construct for combining multiple resultsets. If this union is used frequently, and you want to simplify your T-SQL coding, then implementing those 35 seperate tables as one partitioned table is an option. However, that would probably require much refactoring and would have an unknown impact on performance. A partitioned table could perform worse than a partitioned view; you would have to mock this up in a development database to confirm.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • but union works 40 times i test it..

    run the statement...

    declare @i int

    set @i=0

    declare @v varchar(max)

    set @v=''

    while (@i<40)

    begin

    if (@i=39)

    begin

    set @v=@v+'select '+cast(@i as varchar)

    break

    end

    else

    set @v=@v+'select '+cast(@i as varchar)+' union '

    set @i=@i+1

    end

    exec( @v)

Viewing 10 posts - 1 through 10 (of 10 total)

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