Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527

    Unanswered: performance issue with unions in sp's?

    I was having a chat with a chap over lunch today and he asked if I knew of any performance issues when doing unions in stored procedures. I couldn't think of anything but he seemed sure there was.

    Is there such an issue I've missed?

    Mike

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I think the problem is more with the unions themselves, rather than specifically in stored procedures. In a union, there is an implicit group by (to remove duplicates), which can be painful, especially if you have a lot of columns.
    The other problem is that they can cause extra work (at least in SQL Server). The query
    Code:
    select field1, field2, field3, ...
    from table1
    where field1 = 'A'
    union
    select field1, field2, field3, ...
    from table1
    where field1 = 'B'
    causes two passes through table1. For a large table, that will be a lot of read activity. If field1 is not or can not be adequately indexed, you now have two tablescans on your hands. I find a lot of union queries can be reduced to one pass through the table by using either CASE statements, or by a few OR or IN clauses.

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    thanks for the info.

    Mike

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by MCrowley
    In a union, there is an implicit group by (to remove duplicates), which can be painful, especially if you have a lot of columns.
    it's actually an implicit ORDER BY, and it sorts all rows on all columns

    unless, of course, you specify UNION ALL

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Thanks for the clarification Rudy.
    Mike

    Just for curiosity - wouldn't the "group by" be more likely to remove the duplicates as opposed to the "order by" which would just order them.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yeah, you're right -- GROUP BY on all the columns will remove dupes too

    so i guess it's either an implicit GROUP BY or an implicit ORDER BY with a current/previous row comparison
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    when doing UNIONs, always specify UNION ALL if you can get away with it.

    you can get away with it if you don't care about dupes, or know for a fact that there are no dupes in what you are UNIONing.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •