SQL Server Performance

Using UNION Select Top 5 *

Discussion in 'T-SQL Performance Tuning for Developers' started by surendrakalekar, Nov 17, 2005.

  1. surendrakalekar New Member

    User Northwind

    select top 5 * from customers where city = 'London'
    Union
    select top 5 * from customers where city = 'Sao Paulo'
    order by city desc

    If I execute the above query it is showing me 9 records,
    which includes (5 'London' + 4 'Sao Paulo').
    Instead of 9 I want to display only 5 records in descending order on city.
    So it should show me (4 'Sao Paulo' + 1 'London')



    Surendra Kalekar

  2. Madhivanan Moderator

    Select top 5 * from
    (
    select top 5 * from customers where city = 'London'
    Union
    select top 5 * from customers where city = 'Sao Paulo'
    ) T
    order by city desc

    Madhivanan

    Failing to plan is Planning to fail
  3. druer New Member

    Or you could use SET ROWCOUNT 5 and put your statements in the reverse order so Sao Paulo comes first. If these are really the only 2 you have and this wasn't just an example that is really more complicated:

    SET ROWCOUNT 5
    select * from customers where city = 'Sao Paulo'
    union
    select * from customers where city = 'London'
    SET ROWCOUNT 0


    Using this will also improve your performance because once the ROWCOUNT reaches its limit it will not bother to lookup values period. Meaning if you had 5 Sao Paulo cites it would never actually try and pull any values for London. Whereas the other approach pulls them and then just doesn't bother bringing them back to the client.

    I just put this in for fun, because I assume your query is probably really more complicated than this.
  4. Adriaan New Member

    Problem with your request is that no query will necessarily show you customers from London: only if there are less than 5 customers in Sao Paulo.

    The problem is that it isn't really clear what you want to show, and why. In any case this would require a complex query - and rather a pointless one at that.
  5. null New Member

    W/the limited amount of info you've provided, I agree w/druer. The only exception is that I'd go w/union all instead of union...


    set rowCount 5
    select * from customers where city = 'Sao Paulo'
    union all
    select * from customers where city = 'London'
    set rowCount 0

  6. druer New Member

    That's what I get for copying/pasting the original query. (I do know better)
  7. surendrakalekar New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />In any case this would require a complex query - and rather a pointless one at that.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Thanks everybody for your responce.<br /><b>Adriaan</b>, This query is not pointless [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] this is required for the trial subscribers. We have one SP, which is having union of two queries and paging logic and will display 25 rows on each page. If it is trial subscriber we want to display only top 25 rows.<br /><br /><br /><h6>Surendra Kalekar</h6>
  8. Madhivanan Moderator

    >>The only exception is that I'd go w/union all instead of union...

    Yes. Union All is more efficient that UNION as UNION will unnecessarily select distinct and order the resultset


    Madhivanan

    Failing to plan is Planning to fail
  9. surendrakalekar New Member

    quote:Originally posted by Madhivanan

    >>The only exception is that I'd go w/union all instead of union...

    Yes. Union All is more efficient that UNION as UNION will unnecessarily select distinct and order the resultset


    Madhivanan

    Failing to plan is Planning to fail
    Hi Madhivanan and Null.... Thanks I was unknow about it.
    Can you provide me some good link which will explain with example how UNION ALL is better. Meanwhile I will also try to find it out.


    Surendra Kalekar

  10. surendrakalekar New Member

    quote:Originally posted by Madhivanan

    >>The only exception is that I'd go w/union all instead of union...

    Yes. Union All is more efficient that UNION as UNION will unnecessarily select distinct and order the resultset


    Madhivanan

    Failing to plan is Planning to fail
    Hi Madhivanan and Null.... Thanks I was unknow about it.
    Can you provide me some good link which will explain with example how UNION ALL is better. Meanwhile I will also try to find it out.


    Surendra Kalekar

  11. ghemant Moderator

    Hi,<br />BOL refers :<br /><br />UNION<br />Specifies that multiple result sets are to be combined and returned as a single result set.<br /><br />ALL<br />Incorporates all rows into the results, including duplicates. If not specified, duplicate rows are removed<br /><br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />HTH<br />Regards<br /><br />Hemantgiri S. Goswami<br />ghemant@gmail.com<br />"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri Goswami<br />
  12. Adriaan New Member

    To come back to the 'pointless' bit, from your original post: "Instead of 9 I want to display only 5 records in descending order on city. So it should show me (4 'Sao Paulo' + 1 'London')"

    Guess I must have been hypersensitive to the 'should show' phrase: if you read that as 'must show 4 from Sao Paulo and 1 from London' it would be a pretty ugly query indeed.

    Don't bother with UNION here, unless you test and find that it performs better than WHERE city = 'London' OR city = 'Sao Paulo'.
  13. surendrakalekar New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />To come back to the 'pointless' bit, from your original post: "Instead of 9 I want to display only 5 records in descending order on city. So it should show me (4 'Sao Paulo' + 1 'London')"<br /><br />Guess I must have been hypersensitive to the 'should show' phrase: if you read that as 'must show 4 from Sao Paulo and 1 from London' it would be a pretty ugly query indeed.<br /><br />Don't bother with UNION here, unless you test and find that it performs better than WHERE city = 'London' OR city = 'Sao Paulo'.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Hey Adriaan coooool..[<img src='/community/emoticons/emotion-1.gif' alt=':)' />] just forget that phrase and don't take it seriously in my future posts I will avoid such phrases. It could be better if I paste my code here instead of this example. <br />Anyway I changed that sp is working fine. Thanks once again to everybody.<br /><br /><br /><h6>Surendra Kalekar</h6>
  14. Adriaan New Member

    Surendra, I'm chilling [8D] ...
  15. null New Member

    quote:Originally posted by surendrakalekar
    Hi Madhivanan and Null.... Thanks I was unknow about it.
    Can you provide me some good link which will explain with example how UNION ALL is better. Meanwhile I will also try to find it out.
    Well Madhavinan and ghemant gave you the jist of it...but here...

    http://www.fmsinc.com/free/NewTips/SQL/SQLtip5.asp

    http://www.sql-server-performance.com/transact_sql.asp
    --search on "union", there are a couple of segments that discuss union vs. union all

    I've personally seen some awesome perforance gains from using union all vs. union. The sad part is alot of db programmers (well, @least the ones that don't put forth much effort in writing efficient code) I've talked to overlook the benefits of union all...and some haven't even heard of it (not always a bad thing, like yourself, maybe they've just never been exposed to it).
  16. druer New Member

    I think that the problem is that the normal expectation would be that the default for UNION would be ALL. Since the very nature of using a UNION is similar to saying A + B. Programmers say I want this set of results, and oh by the way also include this set of results. SELECT also has ALL or DISTINCT but the default for it is obviously ALL. So it would only make sense if you say SELECT ALL of these and/UNION SELECT ALL of these that you would get back ALL of the records selected. Of course it doesn't, which is why the pay us the big bucks. Yeah right!

    As you say, many have never heard of it, because they've gotten away without needing it. Usually the first set doesn't return any rows that would be even close to the second set so a DISTINCT default lets them get through. As many don't really worry about performance either, they would never notice the performance gain of using ALL. Funny thing is that I now maintain a database from a 3rd party vendor. Just last week I came across a stored procedure that does some bizarre (very poorly coded T-SQL) things and takes about 3 minutes minimum to perform. I was able to rewrite the stored procedure to run in less than 20 seconds, but oddly enough the only thing that they had coded properly was the UNION ALL. What was funny is that they were selecting completely distinct data from 3 different tables, and unlike many other stored procedures that they had written with just UNION, they actually had it spelled out in this case. So in the end the piece that took less than .02% of the overall time to complete was the only piece that was written and performed well.




  17. null New Member

    quote:Originally posted by Adriaan
    Don't bother with UNION here, unless you test and find that it performs better than WHERE city = 'London' OR city = 'Sao Paulo'.
    I did test the diff approaches, on a few different criteria. The approach w/Union All should peform better...well, @least it did in every instance when I tested it. The difference being that w/out the union all, he'd have to include an order by. If I have time today I'll put some script up.
  18. null New Member

    druer,

    You're right on, most the guys I've talked to never found a "need" to use Union All. Many of our developers are just after the dang "result set", and don't really bother how they get it, then they wonder why their stuff runs terribly. Check out this snip of code I dug out (pseudo form)...this was one of many qry's I re-wrote a couple of projects ago...

    selectdistinct questionID
    from(
    selectdistinct [table 1 primary key field] as questionID
    from table1
    where[a bunch of other stuff]
    union
    selectdistinct [table 2 primary key field] as questionID
    from table2
    where[a bunch of other stuff]
    ) as A
    orderby questionID
    ...surely we can see the over kill, talk about doing more stuff than you really need to. The rewritten version ran about 6 times faster, and the developer was asking "how?". No magic I told him...you just need to be fully aware of what you're telling the db to do, and more importantly, don't tell it to do more than it needs to for a given purpose.

    Edit: Changed "select distinct [table 2 primary key field] as as questionID" to "selectdistinct [table 2 primary key field] as questionID"
  19. Madhivanan Moderator

    Good Points null [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  20. derrickleggett New Member

    You're hired null. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] SQL Server is all about how to resolve to the smallest size and amount of data sets the quickest to arrive at a particular answer. You should ALWAYS think about that when programming SQL. It's set-based. The most efficient set is one that's small and well defined. Utilization of indexes, avoidance of loops, and quick identification and filtering of only necessary data will provide results of data faster than any other types of language out there. Getting object-oriented programmers to think this way though can be one of the biggest challenges for a DBA.<br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  21. Madhivanan Moderator

    null it seems that you have edited your previous reply
    Can you highlight the changes you made?

    Madhivanan

    Failing to plan is Planning to fail
  22. null New Member

    Sure thing, I came back to the thread and noticed a small typo, I noted it below the reply.

    Btw, thx for the positive comments. I still consider myself relatively green to db development, but I've learned a ton since I've been out of college. Database has really turned into one of my primary interests, look fwd to sticking around and picking up a thing or two.
  23. surendrakalekar New Member

    Hey Null Thanks a lot. <br />It's a very good clarification on UNION ALL. Now I need to check our existing SP#%92s for UNION ALL performance issue <img src='/community/emoticons/emotion-1.gif' alt=':)' />. <br /><br /><br /><h6>Surendra Kalekar</h6>

Share This Page