Performance Question - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Performance Question

Author

Topic

MichaelP
Jedi Yak

2489 Posts

Posted - 2001-12-13 : 15:24:05

Are there faster ways to do a query similar to this? My table has about 500k rows, and it growing quickly. I'm currently getting many timeouts while running this Stored Proc. I've even adjusted the CommandTimeout property of my command object, and that doesn't seem to help either.

SELECT fq.CostCenterID, min(fq.cre_date) as Cre_Date, Count(*) as TotalFaxes,
(select count(*) from faxqueue where ((faxstatus=0) or (faxstatus = 3 and attempts < numberofattempts)) and (costCenterID = fq.CostCenterID)) as TotalUnSent,
(select count(*) from faxqueue where faxstatus=1 and costCenterID = fq.CostCenterID) as TotalInUse,
(select count(*) from faxqueue where faxstatus=2 and costCenterID = fq.CostCenterID) as TotalSent,
(select count(*) from faxqueue where ((faxstatus=3) and (attempts = numberofattempts)) and (costCenterID = fq.CostCenterID)) as TotalError,
(select count(*) from faxqueue where faxstatus=4 and costCenterID = fq.CostCenterID) as TotalPaused

FROM faxqueue fq
WHERE siteID = @SiteID
AND fq.cre_date BETWEEN @BeginDate AND DATEADD(dd, 1, @EndDate)
GROUP BY fq.CostCenterID
ORDER BY Cre_Date DESC

GreatInca
Posting Yak Master

102 Posts

Posted - 2001-12-13 : 17:03:48

Use a Case Statment. Your curent query runs each of those 5 queries for every CostCenterID in your table. This should run everything just once. Remember aggragates ignore NULLs (except Count(*)).

SELECT
fq.CostCenterID, min(fq.cre_date) as Cre_Date, Count(*) as TotalFaxes,
COUNT(CASE WHEN ((faxstatus=0) or (faxstatus = 3 and attempts < numberofattempts)) THEN 1 ELSE NULL END) as TotalUnSent,
COUNT(CASE WHEN faxstatus=1 THEN 1 ELSE NULL END) AS TotalInUse,
COUNT(CASE WHEN faxstatus=2 THEN 1 ELSE NULL END) AS TotalSent,
COUNT(CASE WHEN faxstatus=3 AND attempts = numberofattempts THEN 1 ELSE NULL END)) AS TotalError,
COUNT(CASE WHEN faxstatus=4 THEN 1 ELSE NULL END) AS TotalPaused
FROM faxqueue fq
WHERE siteID = @SiteID
AND fq.cre_date BETWEEN @BeginDate AND DATEADD(dd, 1, @EndDate)
GROUP BY fq.CostCenterID
ORDER BY Cre_Date DESC

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2001-12-13 : 17:11:56

I'm suspicious of the correctness of this [the questioner's] query. It might be right, but it does look a bit odd. Your outer SELECT is looking only at faxes for a particular site and time range of ?creation, with a count for each cost center called TotalFaxes. The subselects, however, are working on all the faxes for a cost center irrespective of site and time. Is that right?
If so, reducing the number of joins by treating all the subselects in one go and counting cases might help your cause (though some judicious indexing might help even more):


SELECT fq.CostCenterID, min(fq.cre_date) as Cre_Date, Count(*) as TotalFaxes,
  count(case when fq2.faxstatus=0 or (fq2.faxstatus = 3 and
      fq2.attempts < fq2.numberofattempts) then 1 else null end) as TotalUnSent,
  count(case when fq2.faxstatus=1 then 1 else null end) as TotalInUse,
  count(case when fq2.faxstatus=2 then 1 else null end) as TotalSent,
  count(case when fq2.faxstatus=3 and
      fq2.attempts = fq2.numberofattempts then 1 else null end) as TotalError,
  count(case when fq2.faxstatus=4 then 1 else null end) as TotalPaused
FROM faxqueue fq
INNER JOIN faxqueue fq2 ON fq.costCenterID = fq2.CostCenterID
WHERE fq.siteID =  @SiteID
  AND fq.cre_date BETWEEN @BeginDate AND DATEADD(dd, 1, @EndDate)
GROUP BY fq.CostCenterID
ORDER BY Cre_Date DESC

That's untested so there might be typos.
If your subselects are supposed to be restricted by site and date range as the main select then you can throw away the join:


SELECT CostCenterID, min(cre_date) as Cre_Date, Count(*) as TotalFaxes,
  count(case when faxstatus=0 or (faxstatus = 3 and
      attempts < numberofattempts) then 1 else null end) as TotalUnSent,
  count(case when faxstatus=1 then 1 else null end) as TotalInUse,
  count(case when faxstatus=2 then 1 else null end) as TotalSent,
  count(case when faxstatus=3 and
      attempts = numberofattempts then 1 else null end) as TotalError,
  count(case when faxstatus=4 then 1 else null end) as TotalPaused
FROM faxqueue
WHERE siteID =  @SiteID
  AND cre_date BETWEEN @BeginDate AND DATEADD(dd, 1, @EndDate)
GROUP BY CostCenterID
ORDER BY Cre_Date DESC

In passing, I note by using BETWEEN, the midnight of the day following @EndDate is included. If cre_date is shortdatetime, this is a minute, if datetime, only 1/300 sec.

Gah! Beaten to it.

Edited by - Arnold Fribble on 12/13/2001 17:13:29

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources