Adding 2 COUNT statements results in heavy query

Question

Post reply

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: 1004494 More actions · Answer 3

Gail Shaw

SSC Guru

Points: 1004494

January 28, 2014 at 4:42 am

#1683863

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

petervdkerk

SSCrazy

Points: 2155

January 28, 2014 at 4:55 am

#1683866

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: 1004494 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