Adding 2 COUNT statements results in heavy query

Question

Adding 2 COUNT statements results in heavy query

petervdkerk

SSCrazy

Points: 2155
More actions
January 28, 2014 at 3:52 am

#303827

These separate COUNT queries are very fast:
SELECT COUNT(id) as viewcount from location_views WHERE createdate>DATEADD(dd,-30,getdate()) AND objectid=357
SELECT COUNT(id)*2 as clickcount FROM extlinks WHERE createdate>DATEADD(dd,-30,getdate()) AND objectid=357
But I want to add the COUNT statements, so this is what I did:
select COUNT(vws.id)+COUNT(lnks.id)*2 AS totalcount
FROM location_views vws,extlinks lnks
WHERE (vws.createdate>DATEADD(dd,-30,getdate()) AND vws.objectid=357)
OR
(lnks.createdate>DATEADD(dd,-30,getdate()) AND lnks.objectid=357)
Turns out the query becomes immensely slow. There must be something I'm doing wrong here which results in such bad performance, but what is it?
Thanks!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

You didn't define how to join the 2 views. By the way I'm not sure that you should define a join. If all you want is to add those 2 numbers, you can do it this way:

select (SELECT COUNT(id) as viewcount from location_views WHERE createdate>DATEADD(dd,-30,getdate()) AND objectid=357) +

(SELECT COUNT(id)*2 as clickcount FROM extlinks WHERE createdate>DATEADD(dd,-30,getdate()) AND objectid=357)

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

petervdkerk SSCrazy Points: 2155 More actions · Answer 2

petervdkerk

SSCrazy

Points: 2155

January 28, 2014 at 4:10 am

#1683856

So simple...thanks!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Why count(ID), not count(*)?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

petervdkerk SSCrazy Points: 2155 More actions · Answer 4

I always thought it was best practice to add a columnname since * will slow performance in case of NULL values

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Other way around.

http://sqlinthewild.co.za/index.php/2009/04/14/on-counts/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

petervdkerk SSCrazy Points: 2155 More actions · Answer 6

Wow, I've misunderstood that all these years! Thanks! 🙂

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

If memory serves the only time you want the engine to count a particular field is when you want it to DISCARD NULL values from the count.

Oh, and I would have done this using a UNION ALL construct of the two counts.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service