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
Aleksdepends what your UNION ALL query does :)

if you mean this --select 'a' as tablename, count(*) as rows from tablea
union all select 'b', count(*) from tableb
union all select 'c', count(*) from tablecthen this would be faster|||can you explain why?|||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 ;)|||good enough...thanks|||What about other?|||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|||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|||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?|||yes, i am

are you?|||Are you saying that having 3 jobs launch at the same time is difficult?|||depends what your UNION ALL query does :)

if you mean this --select 'a' as tablename, count(*) as rows from tablea
union all select 'b', count(*) from tableb
union all select 'c', count(*) from tablecthen this would be fasterJust to muddy the waters further - faster than what?|||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|||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|||With my eyes closed?

Again, it comes down to what they want to do with the results...|||i see things are interesting here...but here is the actual scenario
i am working on mobile device that has a db on an SD card....
currently, i am rewriting a form that someone has previously designed, prior to my work on the form, client complained that the form is very slow during load time. on the form, there is a drop down and 3 label fields. each field is a count. the drop down is a straight select all, however, the other three fields are coming from one SELECT query that does count. I wanted to know what would be faster for me, to do 3 different trips to the database or to use the existing SELECT UNION query...|||could we please see the existing SELECT UNION?|||SELECT 'AREA_COUNT' As ItemTitle, COUNT(AREA_ID) As ItemValue FROM AREA WHERE Area.LEADER_ID = {LEADER} GROUP BY Area.LEADER_ID

UNION

SELECT ju.STEP_CODE + '_COUNT' As ItemTitle, COUNT(distinct a.AREA_ID) As ItemValue FROM AREA a INNER JOIN JOB_UNIT ju ON a.AREA_ID = ju.AREA_ID AND ju.COMPLETE_DATE IS NULL INNER JOIN EMPLOYEE_WORK sw ON ju.JOB_UNIT_ID = sw.JOB_UNIT_ID AND sw.REASSIGN_REASON_CODE IS NULL WHERE Area.LEADER_ID = {LEADER} GROUP BY ju.STEP_CODE

UNION

SELECT 'UNASSIGNED_COUNT' As ItemTitle, COUNT(a.AREA_ID) As ItemValue FROM AREA a INNER JOIN USER_CREDENTIAL uc ON RECORD_ID = 0 INNER JOIN EMPLOYEE s ON uc.FDCA_EMPLOYEE_ID = s.FDCA_EMPLOYEE_ID INNER JOIN JOB_UNIT ju ON a.AREA_ID = ju.AREA_ID AND s.FLD_OPCODE = ju.FLD_OPCODE AND ju.COMPLETE_DATE IS NULL LEFT OUTER JOIN EMPLOYEE_WORK sw ON ju.JOB_UNIT_ID = sw.JOB_UNIT_ID AND sw.REASSIGN_REASON_CODE IS NULL WHERE sw.JOB_UNIT_ID IS NULL AND Area.LEADER_ID = {LEADER} GROUP BY Area.LEADER_ID

UNION

SELECT 'COMPLETE_COUNT' As ItemTitle, COUNT(a.AREA_ID) As ItemValue FROM AREA a INNER JOIN USER_CREDENTIAL uc ON RECORD_ID = 0 INNER JOIN EMPLOYEE s ON uc.FDCA_EMPLOYEE_ID = s.FDCA_EMPLOYEE_ID INNER JOIN JOB_UNIT ju ON a.AREA_ID = ju.AREA_ID AND s.FLD_OPCODE = ju.FLD_OPCODE AND ju.COMPLETE_DATE IS NOT NULL WHERE Area.LEADER_ID = {LEADER} GROUP BY Area.LEADER_ID|||more efficient would be to use UNION ALL so sql doesn't have to sort. but it will be a negligible difference. you'd notice it more if each query were returning many rows.

in general it's better to use UNION ALL instead of UNION if you can get away with it though.|||FYI when you do this --

... WHERE xxx.LEADER_ID = {LEADER} GROUP BY xxx.LEADER_ID

then if xxx.LEADER_ID is not mentioned in the SELECT, you can go ahead and eliminate the GROUP BY clause|||What kinda join is this?

FROM AREA a
INNER JOIN USER_CREDENTIAL uc
ON RECORD_ID = 0|||Yes remove to group by and convert the union's

What does the show plan say? Do you have any index or table scans?

SELECT 'AREA_COUNT' As ItemTitle
, COUNT(AREA_ID) As ItemValue
FROM AREA
WHERE Area.LEADER_ID = {LEADER}
--GROUP BY Area.LEADER_ID
UNION ALL
SELECT ju.STEP_CODE + '_COUNT' As ItemTitle
, COUNT(distinct a.AREA_ID) As ItemValue
FROM AREA a
INNER JOIN JOB_UNIT ju
ON a.AREA_ID = ju.AREA_ID
AND ju.COMPLETE_DATE IS NULL
INNER JOIN EMPLOYEE_WORK sw
ON ju.JOB_UNIT_ID = sw.JOB_UNIT_ID
AND sw.REASSIGN_REASON_CODE IS NULL
WHERE Area.LEADER_ID = {LEADER}
--GROUP BY ju.STEP_CODE
UNION ALL
SELECT 'UNASSIGNED_COUNT' As ItemTitle
, COUNT(a.AREA_ID) As ItemValue
FROM AREA a
INNER JOIN USER_CREDENTIAL uc
ON RECORD_ID = 0
INNER JOIN EMPLOYEE s
ON uc.FDCA_EMPLOYEE_ID = s.FDCA_EMPLOYEE_ID
INNER JOIN JOB_UNIT ju
ON a.AREA_ID = ju.AREA_ID
AND s.FLD_OPCODE = ju.FLD_OPCODE
AND ju.COMPLETE_DATE IS NULL
LEFT JOIN EMPLOYEE_WORK sw
ON ju.JOB_UNIT_ID = sw.JOB_UNIT_ID
AND sw.REASSIGN_REASON_CODE IS NULL
WHERE sw.JOB_UNIT_ID IS NULL
AND Area.LEADER_ID = {LEADER}
--GROUP BY Area.LEADER_ID
UNION ALL
SELECT 'COMPLETE_COUNT' As ItemTitle
, COUNT(a.AREA_ID) As ItemValue
FROM AREA a
INNER JOIN USER_CREDENTIAL uc
ON RECORD_ID = 0
INNER JOIN EMPLOYEE s
ON uc.FDCA_EMPLOYEE_ID = s.FDCA_EMPLOYEE_ID
INNER JOIN JOB_UNIT ju
ON a.AREA_ID = ju.AREA_ID
AND s.FLD_OPCODE = ju.FLD_OPCODE
AND ju.COMPLETE_DATE IS NOT NULL
WHERE Area.LEADER_ID = {LEADER}
--GROUP BY Area.LEADER_ID|||indexes are in place for all the primary fields in the tables...i am going to go with UNION ALL, after all there is a lot of data...thank you all|||I'm still at a loss to see what those group by's would do|||well, it seems that i have to use GROUP BY, I am getting an error when I try to compile...when i do use the group by clause the error goes away...

the error i am getting is Invalid Expression|||when i do use the group by clause the error goes away...one of the best. reasons. ever.

say, what about that interesting join that brett asked you about?|||well, it seems that i have to use GROUP BY, I am getting an error when I try to compile...when i do use the group by clause the error goes away...

the error i am getting is Invalid Expression

Well that's not a good reason...

Post the DDL of your tables

And the indexes (or is that indicies?)|||well, it seems that i have to use GROUP BY, I am getting an error when I try to compile...when i do use the group by clause the error goes away...

another way to achieve error free execution is to comment out the whole query.

it would also run faster that way.|||another way to achieve error free execution is to comment out the whole query.

it would also run faster that way.

U FUNNY

But don't give up the day job just yet|||I actually had a boss one time that fancied himself an expert in t-sql.

He once claimed he fixed a bug in one of his procs by removing a comment.

I don't work there anymore thankfully. the company has since popped.|||I just wonder what kind of meaning that queries has...look at this sample

USE Northwind

SELECT 'Label1' AS Source, COUNT(*)
FROM orders
GROUP BY CustomerID
UNION ALL
SELECT 'Label2' AS Source, COUNT(*)
FROM [order details]
GROUP BY ProductID

Kind of the same thing he''s doig...no?