sql stored procedure uses up the cpu

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)

sql stored procedure uses up the cpu

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-07-22 : 06:47:29

i have the following stored procedure that runs often.
it runs in less than 2 seconds but puts the cpu to 100% as it runs.

Is there anything I can do to stop that?


ALTER PROCEDURE [dbo].[overview]
	
AS
BEGIN
declare @today datetime,@yesterday datetime
select @today=getdate()
select @yesterday=getdate()-1

select site, count( all case when date between DATEADD(ww, -1, @yesterday) AND  @yesterday then 1 else null end) as w1 ,
count( all case when date between DATEADD(ww, -2, @yesterday) AND DATEADD(ww, -1, @yesterday) then 1 else null end) as w2,
count( all case when date between DATEADD(ww, -3, @yesterday) AND DATEADD(ww, -2, @yesterday) then 1 else null end) as w3,
count( all case when date between DATEADD(ww, -4, @yesterday) AND DATEADD(ww, -3, @yesterday) then 1 else null end) as w4,
count( all case when date>=dateadd(day,datediff(day,0,getdate()),0) and date<dateadd(day,datediff(day,0,getdate()+1),0) then 1 else null end) as totaltoday,
count( all case when date >= dateadd(hh,8,dateadd(d,datediff(d,'2008-01-01',@today),'2008-01-01'))  and date  < dateadd(hh,9,dateadd(d,datediff(d,'2008-01-01',@today),'2008-01-01')) then 1 else null end) as h8,
count( all case when date >= dateadd(hh,9,dateadd(d,datediff(d,'2008-01-01',@today),'2008-01-01'))  and date  < dateadd(hh,10,dateadd(d,datediff(d,'2008-01-01',@today),'2008-01-01')) then 1 else null end) as h9,
count( all case when date >= dateadd(hh,10,dateadd(d,datediff(d,'2008-01-01',@today),'2008-01-01'))  and date  < dateadd(hh,11,dateadd(d,datediff(d,'2008-01-01',@today),'2008-01-01')) then 1 else null end) as h10,
count( all case when date >= dateadd(hh,11,dateadd(d,datediff(d,'2008-01-01',@today),'2008-01-01'))  and date  < dateadd(hh,12,dateadd(d,datediff(d,'2008-01-01',@today),'2008-01-01')) then 1 else null end) as h11,
count( all case when date >= dateadd(hh,12,dateadd(d,datediff(d,'2008-01-01',@today),'2008-01-01'))  and date  < dateadd(hh,13,dateadd(d,datediff(d,'2008-01-01',@today),'2008-01-01')) then 1 else null end) as h12,
count( all case when date >= dateadd(hh,13,dateadd(d,datediff(d,'2008-01-01',@today),'2008-01-01'))  and date  < dateadd(hh,14,dateadd(d,datediff(d,'2008-01-01',@today),'2008-01-01')) then 1 else null end) as h13,
count( all case when date >= dateadd(hh,14,dateadd(d,datediff(d,'2008-01-01',@today),'2008-01-01'))  and date  < dateadd(hh,15,dateadd(d,datediff(d,'2008-01-01',@today),'2008-01-01')) then 1 else null end) as h14,
count( all case when date >= dateadd(hh,15,dateadd(d,datediff(d,'2008-01-01',@today),'2008-01-01'))  and date  < dateadd(hh,16,dateadd(d,datediff(d,'2008-01-01',@today),'2008-01-01')) then 1 else null end) as h15,
count( all case when date >= dateadd(hh,16,dateadd(d,datediff(d,'2008-01-01',@today),'2008-01-01'))  and date  < dateadd(hh,17,dateadd(d,datediff(d,'2008-01-01',@today),'2008-01-01')) then 1 else null end) as h16,


max(dateinserted) as dateinserted

from images
where date>DATEADD(ww, -4, @yesterday)
group by site order by totaltoday,w1,w2,w3,w4 asc
return END

rv
Starting Member

11 Posts

Posted - 2008-07-22 : 11:31:21

Which Version of SQL you are using ?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-22 : 11:38:21

quote:
Originally posted by rv

Which Version of SQL you are using ?

Since she has put in SQL 2005 forum so it should be 2005 right.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-07-22 : 11:48:57

yes it's 2005

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-07-22 : 23:27:42

Possible because of those date functions used.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-07-23 : 00:50:55

is there anything i can do to optimize it?

pootle_flump

1064 Posts

Posted - 2008-07-23 : 06:17:07

I would remove the order by and let the client application order the data.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-07-23 : 06:53:27

you think order by is making it use the cpu more?
anyway to optimize all the dates in anyway?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-23 : 07:33:46

Try this

ALTER PROCEDURE dbo.Overview
AS

DECLARE	@Today DATETIME,
	@Yesterday DATETIME,
	@Tomorrow DATETIME,
	@Past DATETIME

SELECT	@Today = DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '19000101'),
	@Yesterday = DATEADD(DAY, -1, @Today),
	@Tomorrow = DATEADD(DAY, 1, @Today),
	@Past = DATEADD(DAY, -28, @Today)

SELECT		[Site],
		SUM(CASE WHEN theWeek = 0 THEN 1 ELSE 0 END) AS Week1,
		SUM(CASE WHEN theWeek = 1 THEN 1 ELSE 0 END) AS Week2,
		SUM(CASE WHEN theWeek = 2 then 1 ELSE 0 END) AS Week3,
		SUM(CASE WHEN theWeek = 3 then 1 ELSE 0 END) AS Week4,
		SUM(theCurrent) AS TotalToday,
		SUM(CASE WHEN theHour = 8 THEN 1 ELSE 0 END) AS Hour8,
		SUM(CASE WHEN theHour = 9 THEN 1 ELSE 0 END) AS Hour9,
		SUM(CASE WHEN theHour = 10 THEN 1 ELSE 0 END) AS Hour10,
		SUM(CASE WHEN theHour = 11 THEN 1 ELSE 0 END) AS Hour11,
		SUM(CASE WHEN theHour = 12 THEN 1 ELSE 0 END) AS Hour12,
		SUM(CASE WHEN theHour = 13 THEN 1 ELSE 0 END) AS Hour13,
		SUM(CASE WHEN theHour = 14 THEN 1 ELSE 0 END) AS Hour14,
		SUM(CASE WHEN theHour = 15 THEN 1 ELSE 0 END) AS Hour15,
		SUM(CASE WHEN theHour = 16 THEN 1 ELSE 0 END) AS Hour16,
		MAX(DateInserted) AS DateInserted
FROM		(
			SELECT	[Site],
				DateInserted,
				DATEDIFF(DAY, Date, @Yesterday) / 7 AS theWeek,
				DATEDIFF(HOUR, @Today, Date) AS theHour
				CASE
					WHEN Date >= @Today AND Date < @Tomorrow THEN 1
					ELSE 0
				END AS theCurrent
			FROM	Images
			WHERE	Date >= @Past
				AND Date < @Tomorrow
		) AS i
GROUP BY	[Site]
ORDER BY	6,
		2,
		3,
		4,
		5