Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    Join Date
    Jun 2007
    Posts
    7

    Unanswered: 3 count queries vs UNION ALL

    hi all,

    i am not sure what would be more optimum for speed, i was wondering if one is better than the other.

    i have 3 count queries that i can write as
    SELECT COUNT(a) FROM ....
    SELECT COUNT(b) FROM ....
    SELECT COUNT(c) FROM ....

    or i can have them in UNION ALL....

    which would be faster three seperate queries or one UNION ALL query, any help?

    thanks
    Aleks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    depends what your UNION ALL query does

    if you mean this --
    Code:
    select 'a' as tablename, count(*) as rows from tablea
    union all select 'b', count(*) from tableb
    union all select 'c', count(*) from tablec
    then this would be faster
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2007
    Posts
    7

    thanks

    can you explain why?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    let's pretend that you and i are sitting in new york, and i ask you to go and find out how many men, women, and children there are in los angeles

    so you get on a plane, go to los angeles, then come back to new york and say "there are X men in los angeles"

    then you get on a plane, go to los angeles, then come back to new york and say "there are Y women in los angeles"

    finally, you get on a plane, go to los angeles, then come back to new york and say "there are Z children in los angeles"

    compare the above to this --

    you get on a plane, go to los angeles, then come back to new york and say "there are X men, Y women, and Z children in los angeles"

    that's why
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2007
    Posts
    7

    good enough...thanks

    good enough...thanks

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What about other?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Actually, aside from my poor atteempt at humor...If you did each count in an indeoendent thread and the launched them together so they went off asynchronously, then that might be faster

    So, instead of just 1 agent (you flying back and forth in Rudy's example), there are 3 of you flying out at the same time, and instead of you having to doing the counting by yourself, you would have 2 other people doing the counting

    but this sounds very much like a class assignement, and I'm sure the instructor wants the answer Rudy gave
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    launch asynchronous threads in sql server?

    please, do tell how this might be accomplished

    i'm betting you lose more of your time (at $NNN/hour) setting this up than you will ever hope to save (at $0.000000000NNN/hour) in machine time over the life of the app
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by r937
    launch asynchronous threads in sql server?

    please, do tell how this might be accomplished

    i'm betting you lose more of your time (at $NNN/hour) setting this up than you will ever hope to save (at $0.000000000NNN/hour) in machine time over the life of the app
    Are you serious?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, i am

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

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Are you saying that having 3 jobs launch at the same time is difficult?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    depends what your UNION ALL query does

    if you mean this --
    Code:
    select 'a' as tablename, count(*) as rows from tablea
    union all select 'b', count(*) from tableb
    union all select 'c', count(*) from tablec
    then this would be faster
    Just to muddy the waters further - faster than what?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I think Ruday means faster than making 3 trips, even using the same spid, like OP suggested

    SELECT COUNT(a) FROM ....
    SELECT COUNT(b) FROM ....
    SELECT COUNT(c) FROM ....

    The better question is, what do they want to do with the results
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Brett Kaiser
    Are you saying that having 3 jobs launch at the same time is difficult?
    it is if you don't know how to "launch" a "job" -- and then, presumably, collate the results, yes?

    as i said before, i'm betting you lose more of your time (at $NNN/hour) setting this up than you will ever hope to save (at $0.000000000NNN/hour) in machine time over the life of the app
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    With my eyes closed?


    Again, it comes down to what they want to do with the results...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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