Alternative for COUNT(column_name)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Alternative for COUNT(column_name)

Author

Topic

naveendkt
Starting Member

5 Posts

Posted - 2011-08-19 : 16:24:39

I have a Query something like below

SELECT S.StoreID,S.LocationName,ISNULL(tmp7.WithoutChild,'0'),ISNULL(tmp3.ThreeToEight,'0')
FROM Store S
LEFT JOIN (SELECT COUNT(DISTINCT R.ReservationID) AS WithoutChild,R.StoreID FROM Reservation R
INNER JOIN Customer C ON C.CustomerID = R.CustomerID
WHERE (R.ReservationID NOT IN(SELECT ReservationID FROM ReservationChild)) AND (R.StartingTime BETWEEN '08/15/2011' AND '08/19/2011')
GROUP BY R.StoreID) AS tmp7 ON tmp7.StoreID = S.StoreID

LEFT JOIN (SELECT COUNT(DISTINCT R.ReservationID) AS ThreeToEight,R.StoreID FROM Reservation R
INNER JOIN Customer C ON C.CustomerID = R.CustomerID
INNER JOIN ReservationChild RC ON RC.ReservationID = R.ReservationID
WHERE (R.StartingTime BETWEEN '08/15/2011' AND '08/19/2011') AND (RC.Age BETWEEN 9 AND 12)
GROUP BY R.StoreID) AS tmp3 ON tmp3.StoreID = S.StoreID

I have added only 2 left joins here,Actual query has 7. Each is a temporary table with COUNT() as one of its column.

This query times out when the time range is given large.

BTW this query id running inside a SP. Do I have any option of decreasing the execution time for this SP?

Other Info: Reservation table has around 1819320 rows and Store table has 210.

FYI:The query will return rows in Store table and columns are Count with different conditions from Reservation table.

Naveen

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-20 : 01:54:23

try to merge it like this and see


SELECT S.StoreID,S.LocationName,ISNULL(tmp7.WithoutChild,'0'),ISNULL(tmp7.ThreeToEight,'0')
FROM Store S 
LEFT JOIN (SELECT COUNT(DISTINCT R.ReservationID) AS WithoutChild,
COUNT(DISTINCT CASE WHEN RC.ReservationID IS NOT NULL
AND RC.Age BETWEEN 9 AND 12 THEN RC.ReservationID ELSE NULL END) AS ThreeToEight,
R.StoreID FROM Reservation R 
INNER JOIN Customer C ON C.CustomerID = R.CustomerID
LEFT JOIN  ReservationChild RC ON RC.ReservationID = R.ReservationID 
WHERE (R.StartingTime BETWEEN '08/15/2011' AND '08/19/2011')
GROUP BY R.StoreID) AS tmp7 ON tmp7.StoreID = S.StoreID

also why do you need DISTINCT inside count? will reservationid repeat for customer? also if you dont want to return any customer info why include it in join then?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-20 : 01:55:08

Also check whether you've required indexes on joining columns by analysing execution plan.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

naveendkt
Starting Member

5 Posts

Posted - 2011-08-20 : 13:46:43

Thanks visakh16 for your reply.

For your first question. No the reservationid won't repeat in Reservation table. I have removed the Distinct inside COUNT since its a primary key.

I have Inner joined Customer because there are reservations without customerid, i don't want those reservations.

There is index defined for all the columns which is used to join.

I am going to try as you suggested soon. And I will let you know the result.

Also as I told I am using this inside SP. So if i use temporary tables to get the counts one by one will that help? Since there is left joins used to get each count in my query, is that affecting the execution time?

Thank you,
Naveen

Naveen

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-20 : 13:57:42

no need of even temporary table. you can first do main insert and then use a series of updates to get other count values one by one

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources