December 7, 2010 at 9:41 am
Hi,
I am trying to get a set of count results from about 80 bit fields in 1 table of about 250k rows.
I am using the following e.g.
select
ORDEREDCount = (select count(ORDERED) from abstract where ORDERED=1),
CATALOGUEDCount = (select count(CATALOGUED) from abstract where CATALOGUED=1),
PRINTEDCount = (select count(PRINTED) from abstract where PRINTED=1),
PHOTOCOPIEDCount = (select count(PHOTOCOPIED) from abstract where PHOTOCOPIED=1),
PDFCount = (select count(PDF) from abstract where PDF=1)
etc etc
Is this the most efficient way to run it. Or should I go with the union all route. I have tried to use the database engine tuning advisor but it likes to crash when I ask for its help on this one.
Many Thanks for your help/comments in advance.
Thanks,
Oliver
December 7, 2010 at 1:20 pm
This would be the fastest:
select
ORDEREDCount = sum(case when ORDERED=1 then 1 else 0 end),
CATALOGUEDCount = sum(case when CATALOGUED=1 then 1 else 0 end),
PRINTEDCount = sum(case when PRINTED=1 then 1 else 0 end),
etc. etc.
The probability of survival is inversely proportional to the angle of arrival.
December 8, 2010 at 2:31 am
Thank you very much, the query now takes less than a second instead of 5-6 seconds.
Kind Regards,
Oliver
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply