loop date range - 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 2008 Forums

Transact-SQL (2008)

loop date range

Author

Topic

lappin
Posting Yak Master

182 Posts

Posted - 2011-01-21 : 11:17:24

Hi I have a procedure which inserts /updates data for a single day. I have another proc which takes a date range and calls the proc for each day in the range. The method I use uses a number table and a cursor. In testing the daterange takes much longer than the total of the individual single daily runs.
I have tested over a range of dates where no data exists. The daily proc simply exits out in this case. E.g. Calling daily proc for 15 days takes 1 second:
DBCC DROPCLEANBUFFERS
EXECUTE [INS_Daily]
'1 jan 2012'
GO
EXECUTE [INS_Daily]
'2 jan 2012'
..........
..........
EXECUTE [INS_Daily]
'14 jan 2012'
GO
EXECUTE [INS_Daily]
'15 jan 2012'
GO

But calling for date range takes 2 minutes:
DBCC DROPCLEANBUFFERS
Exec [INS_DateRange]
@startdate='1 jan 2012'
, @enddate='15 jan 2012'

Here is the daterange proc:
alter PROCEDURE [dbo].[INS_DateRange] (@startdate datetime,@enddate datetime )
AS

DECLARE @RC int
DECLARE @ThisDate datetime

--RUN Proc once before date incremented
EXECUTE @RC = [INS_Daily]
@startdate

declare @period datetime

declare datecursor cursor forward_only
for Select dateadd(d,Number,@startdate) from dbo.Numbers
where Number <= datediff(d, @startdate, @enddate)
order by Number
open datecursor

while (1=1)
begin

fetch next from datecursor into @period

if @@fetch_status <> 0
break;
EXECUTE @RC = [dbo].[INS_Daily]
@Period

end
close datecursor
deallocate datecursor

GO

---------------------------------
I checked the numbers table performance. It takes < 1 second to run.
declare @startdate datetime,@enddate datetime
set @startdate='1 jan 2012'
set @enddate='15 jan 2012'
Select dateadd(d,Number,@startdate) from dbo.Numbers
where Number <= datediff(d, @startdate, @enddate)
order by Number

Does anyone have an alternative date loop method?

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-01-21 : 12:22:34

>> I have a procedure which inserts /updates data for a single day. I have another proc which takes a date range and calls the proc for each day in the range. The method I use uses a number table and a cursor. In testing the date range takes much longer than the total of the individual single daily runs. <<

I find that hard to believe. Can you post the DDL and the daily procedure code? A clustered index ont he date involved should make things very fast.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-24 : 11:32:45

instead of calling the proc for each date cant you change proc to take date range(start,end) and process everything between. in making so you can avoid first proc altogether

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources