How to get this T-sql to work faster

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

Transact-SQL (2000)

How to get this T-sql to work faster

Author

Topic

tooms
Starting Member

2 Posts

Posted - 2005-07-27 : 14:09:06

I have a table like this


dbo.RawData
[C_datetime]      [C_value]
2005-07-27 11:00       1
2005-07-27 11:01       2
2005-07-27 11:02       1
2005-07-27 11:03       3
2005-07-27 11:04       4
2005-07-27 11:05       1
2005-07-27 11:06       1
2005-07-27 11:07       3
2005-07-27 11:08       1
2005-07-27 11:09       2
2005-07-27 11:10       2
2005-07-27 11:11       1
2005-07-27 11:12       4
2005-07-27 11:13       1
2005-07-27 11:14       1

and I use this T-sql to get the avg(C_value) for each 5 min. interval


declare @Starttime datetime
declare @endtime datetime
declare @Finishtime datetime
declare @value float
set @Starttime = getdate()
set @EndTime = getdate()
set @FinishTime = DATEADD(day, -1, @Starttime)

CREATE TABLE #TBOUT ([C_DateTime] [datetime] NOT NULL PRIMARY KEY,[C_Value] [float] NULL)

while @Endtime >= @FinishTime
 begin
	set @Endtime = DATEADD(second, -300, @Endtime)
	
	Select @value = avg(C_Value) from dbo.RawData where (C_Datetime between @Endtime and @Starttime)
	if @value IS NOT NULL 
	  BEGIN
	    Insert into #TBOUT (C_DateTime, C_Value) values (@Starttime, @value)
	  end

	if @Endtime <= @FinishTime break

	Set @Starttime = @Endtime
 end

Select * from #TBOUT order by C_Datetime
DROP TABLE #TBOUT

output from t-sql is


[C_datetime]      [C_value]
2005-07-27 11:00       11
2005-07-27 11:05       8
2005-07-27 11:10       9

my question is now how can i get it to run faster beause it is very slow and my webpages are timeout on the users

here is a sample with a timespan on 7 days with a interval on 5 min.
Time to query this is 4 min. and 48 sec. and returns 2016 rows, and the server show 100% cpu when processing.

Thank
Thomas

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-07-27 : 14:24:24

[code]
Create Table #n (n int identity(1,1), x int)
Insert Into #n Select x=0 From master.dbo.syscolumns

--Select * From #n

Create Table #myTable (c_Datetime datetime, c_Value int)
Insert Into #myTable
Select '2005-07-27 11:00', 1 Union All
Select '2005-07-27 11:01', 2 Union All
Select '2005-07-27 11:02', 1 Union All
Select '2005-07-27 11:03', 3 Union All
Select '2005-07-27 11:04', 4 Union All
Select '2005-07-27 11:05', 1 Union All
Select '2005-07-27 11:06', 1 Union All
Select '2005-07-27 11:07', 3 Union All
Select '2005-07-27 11:08', 1 Union All
Select '2005-07-27 11:09', 2 Union All
Select '2005-07-27 11:10', 2 Union All
Select '2005-07-27 11:11', 1 Union All
Select '2005-07-27 11:12', 4 Union All
Select '2005-07-27 11:13', 1 Union All
Select '2005-07-27 11:14', 1

Select * From #myTable

Declare @StartDateTime datetime
Select @StartDateTime = min(c_Datetime) From #myTable

Select
BeginTime,
EndTime,
Ttl = sum(c_value)
From
(
Select
BeginTime = dateadd(s,(n-1)*300,@StartDateTime),
EndTime = dateadd(s,n*300,@StartDateTime)
From #n
) A
Inner Join #myTable B
On A.BeginTime <= B.c_Datetime
and A.EndTime > B.c_Datetime
Group By BeginTime, EndTime

Drop Table #myTable

Drop Table #n
[/code]

Corey