WAITFOR DELAY

System: Dell PowerEdge Server with 3 GB RAM, 2.4 GHz Celeron
Software: Microsoft SQL Server 2000 Enterprise running on Windows 2003
Server
Software: Microsoft SQL Server 2000 Enterprise running on Windows 2000
Server

If you run the code below, you'll notice something odd occuring. The
MilliSecond value does not change after a 1Millisecond delay. Is this a bug
or am I doing something wrong?
Any assistance will be greatly appreciated
----------------------------------------------------------------------------
-------
/*
Programmer : Des L. Davis
Date : July 4, 2004
Purpose : Testing Delayed Reaction
*/

CREATE PROCEDURE [sp_TestDelay] AS

DECLARE @DELAYPERIOD NCHAR(12)
SET @DELAYPERIOD = '00:00:00:001'
DECLARE @Retries INT
SET @Retries = 0
DECLARE @MAXTRIES INT
SET @MAXTRIES = 1000 -- Maximum number of tries before timing out
DECLARE @Modified DATETIME

WHILE ( ( @Retries <= @MAXTRIES ) ) BEGIN

SET @Modified = GetDate() -- Set Modification Date

PRINT @Retries
PRINT @Modified
PRINT 'Year = ' + CONVERT( NCHAR(4), DATEPART( YEAR, @Modified ) )
PRINT 'DayOfYear = ' + CONVERT( NCHAR(3), DATEPART( DAYOFYEAR ,
@Modified ) )
PRINT 'Hour = ' + CONVERT( NCHAR(2), DATEPART( HOUR, @Modified ) )
PRINT 'Minute = ' + CONVERT( NCHAR(2), DATEPART( MINUTE, @Modified ) )
PRINT 'Second = ' + CONVERT( NCHAR(2), DATEPART( SECOND, @Modified ) )
PRINT 'MilliSecond = ' + CONVERT( NCHAR(3), DATEPART( MILLISECOND,
@Modified ) )

SET @Retries = @Retries + 1 -- Increment loop counter and retry

WAITFOR DELAY @DELAYPERIOD

END
GO

----------------------------------------------------------------------------
-------

re: WAITFOR DELAY

The SQL Server datetime datatype, and therefore WAITFOR DELAY, is only
accurate to 3.33 milliseconds and rounding may occur. You can see what is
happening with the following

DECLARE @DELAYPERIOD datetime
SET @DELAYPERIOD = '00:00:00:001'
SELECT @DELAYPERIOD

This is documented in the SQL Server 2000 Books Online:

<Excerpt href="tsqlref.chm::/ts_da-db_9xut.htm">

Date and time data from January 1, 1753 through December 31, 9999, to an
accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds
or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007
seconds, as shown in the table.

Example Rounded example
01/01/98 23:59:59.999 1998-01-02 00:00:00.000

01/01/98 23:59:59.995, 1998-01-01 23:59:59.997
01/01/98 23:59:59.996,
01/01/98 23:59:59.997, or
01/01/98 23:59:59.998

01/01/98 23:59:59.992, 1998-01-01 23:59:59.993
01/01/98 23:59:59.993,
01/01/98 23:59:59.994

01/01/98 23:59:59.990 or 1998-01-01 23:59:59.990
01/01/98 23:59:59.991

</Excerpt>

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Des L. Davis" <des.davis@cogeco.ca> wrote in message
news:yq3Gc.34099$XY6.2568035@read2.cgocable.net...[color=blue]
> System: Dell PowerEdge Server with 3 GB RAM, 2.4 GHz Celeron
> Software: Microsoft SQL Server 2000 Enterprise running on Windows 2003
> Server
> Software: Microsoft SQL Server 2000 Enterprise running on Windows 2000
> Server
>
> If you run the code below, you'll notice something odd occuring. The
> MilliSecond value does not change after a 1Millisecond delay. Is this a[/color]
bug[color=blue]
> or am I doing something wrong?
> Any assistance will be greatly appreciated
> --------------------------------------------------------------------------[/color]
--[color=blue]
> -------
> /*
> Programmer : Des L. Davis
> Date : July 4, 2004
> Purpose : Testing Delayed Reaction
> */
>
> CREATE PROCEDURE [sp_TestDelay] AS
>
> DECLARE @DELAYPERIOD NCHAR(12)
> SET @DELAYPERIOD = '00:00:00:001'
> DECLARE @Retries INT
> SET @Retries = 0
> DECLARE @MAXTRIES INT
> SET @MAXTRIES = 1000 -- Maximum number of tries before timing out
> DECLARE @Modified DATETIME
>
> WHILE ( ( @Retries <= @MAXTRIES ) ) BEGIN
>
> SET @Modified = GetDate() -- Set Modification Date
>
> PRINT @Retries
> PRINT @Modified
> PRINT 'Year = ' + CONVERT( NCHAR(4), DATEPART( YEAR, @Modified ) )
> PRINT 'DayOfYear = ' + CONVERT( NCHAR(3), DATEPART( DAYOFYEAR ,
> @Modified ) )
> PRINT 'Hour = ' + CONVERT( NCHAR(2), DATEPART( HOUR, @Modified ) )
> PRINT 'Minute = ' + CONVERT( NCHAR(2), DATEPART( MINUTE,[/color]
@Modified ) )[color=blue]
> PRINT 'Second = ' + CONVERT( NCHAR(2), DATEPART( SECOND, @Modified ) )
> PRINT 'MilliSecond = ' + CONVERT( NCHAR(3), DATEPART( MILLISECOND,
> @Modified ) )
>
> SET @Retries = @Retries + 1 -- Increment loop counter and retry
>
> WAITFOR DELAY @DELAYPERIOD
>
> END
> GO
>
> --------------------------------------------------------------------------[/color]
--[color=blue]
> -------
>
>
>[/color]

re: WAITFOR DELAY

I also notice after running a few tests that WAITFOR seems to have a
resolution of 1/100th second.

Specifying a delay of '00:00:00:003' causes the same delay as if I'd
specified 1/100th second.

WAITFOR DELAY '00:00:00:013' causes a 2/100th second delay (:020 or .02), so
it apparently always rounds up.

The behavior seems easier to follow with less output.


alter PROCEDURE [sp_TestDelay] AS
DECLARE @DELAYPERIOD DATETIME
SET @DELAYPERIOD = '00:00:00:013'
DECLARE @Retries INT
SET @Retries = 0
WHILE ( @Retries <= 1000 ) BEGIN
PRINT DATEPART( MILLISECOND, getdate() )
SET @Retries = @Retries + 1 -- Increment loop counter and retry
WAITFOR DELAY @DELAYPERIOD
END
GO

exec sp_testdelay

577
597
617
637
657
.... etc.


"Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
news:rS4Gc.5251$R36.1439@newsread2.news.pas.earthl ink.net...
The SQL Server datetime datatype, and therefore WAITFOR DELAY, is only
accurate to 3.33 milliseconds and rounding may occur. You can see what is
happening with the following

DECLARE @DELAYPERIOD datetime
SET @DELAYPERIOD = '00:00:00:001'
SELECT @DELAYPERIOD

This is documented in the SQL Server 2000 Books Online:

<Excerpt href="tsqlref.chm::/ts_da-db_9xut.htm">

Date and time data from January 1, 1753 through December 31, 9999, to an
accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds
or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007
seconds, as shown in the table.

Example Rounded example
01/01/98 23:59:59.999 1998-01-02 00:00:00.000

01/01/98 23:59:59.995, 1998-01-01 23:59:59.997
01/01/98 23:59:59.996,
01/01/98 23:59:59.997, or
01/01/98 23:59:59.998

01/01/98 23:59:59.992, 1998-01-01 23:59:59.993
01/01/98 23:59:59.993,
01/01/98 23:59:59.994

01/01/98 23:59:59.990 or 1998-01-01 23:59:59.990
01/01/98 23:59:59.991

</Excerpt>

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Des L. Davis" <des.davis@cogeco.ca> wrote in message
news:yq3Gc.34099$XY6.2568035@read2.cgocable.net...[color=blue]
> System: Dell PowerEdge Server with 3 GB RAM, 2.4 GHz Celeron
> Software: Microsoft SQL Server 2000 Enterprise running on Windows 2003
> Server
> Software: Microsoft SQL Server 2000 Enterprise running on Windows 2000
> Server
>
> If you run the code below, you'll notice something odd occuring. The
> MilliSecond value does not change after a 1Millisecond delay. Is this a[/color]
bug[color=blue]
> or am I doing something wrong?
> Any assistance will be greatly appreciated
> --------------------------------------------------------------------------[/color]
--[color=blue]
> -------
> /*
> Programmer : Des L. Davis
> Date : July 4, 2004
> Purpose : Testing Delayed Reaction
> */
>
> CREATE PROCEDURE [sp_TestDelay] AS
>
> DECLARE @DELAYPERIOD NCHAR(12)
> SET @DELAYPERIOD = '00:00:00:001'
> DECLARE @Retries INT
> SET @Retries = 0
> DECLARE @MAXTRIES INT
> SET @MAXTRIES = 1000 -- Maximum number of tries before timing out
> DECLARE @Modified DATETIME
>
> WHILE ( ( @Retries <= @MAXTRIES ) ) BEGIN
>
> SET @Modified = GetDate() -- Set Modification Date
>
> PRINT @Retries
> PRINT @Modified
> PRINT 'Year = ' + CONVERT( NCHAR(4), DATEPART( YEAR, @Modified ) )
> PRINT 'DayOfYear = ' + CONVERT( NCHAR(3), DATEPART( DAYOFYEAR ,
> @Modified ) )
> PRINT 'Hour = ' + CONVERT( NCHAR(2), DATEPART( HOUR, @Modified ) )
> PRINT 'Minute = ' + CONVERT( NCHAR(2), DATEPART( MINUTE,[/color]
@Modified ) )[color=blue]
> PRINT 'Second = ' + CONVERT( NCHAR(2), DATEPART( SECOND, @Modified ) )
> PRINT 'MilliSecond = ' + CONVERT( NCHAR(3), DATEPART( MILLISECOND,
> @Modified ) )
>
> SET @Retries = @Retries + 1 -- Increment loop counter and retry
>
> WAITFOR DELAY @DELAYPERIOD
>
> END
> GO
>
> --------------------------------------------------------------------------[/color]
--[color=blue]
> -------
>
>
>[/color]

re: WAITFOR DELAY

Standard Windows time API calls are only accurate to about 10 ms and this is
apparently how SQL Server implements WAITFOR DELAY. When I run a Profiler
trace, I see statements that run quickly have a reported duration of either
0 , 13 or 16 milliseconds but nothing in between.

Below is my test script that shows the actual behavior of WAITFOR DELAY.
The bottom line is that SQL Server will wait for at least the specified time
but can wait longer due to the combination of datetime rounding and timer
interval resolution.

SET NOCOUNT ON
IF OBJECT_ID('tempdb..#Metrics') IS NOT NULL DROP TABLE #Metrics
CREATE TABLE #Metrics
(
MillisecondDelay int NOT NULL,
MillisecondDelayTime datetime NOT NULL,
Retry int NOT NULL,
StartTime datetime NOT NULL,
EndTime datetime NOT NULL
)
DECLARE @MillisecondDelay int
DECLARE @MillisecondDelayTime datetime
DECLARE @Retries int
DECLARE @StartTime datetime
DECLARE @EndTime datetime

SET @MillisecondDelay = 0
WHILE @MillisecondDelay < 50
BEGIN
SET @MillisecondDelay = @MillisecondDelay + 1
SET @MillisecondDelayTime = DATEADD(ms, @MillisecondDelay,
'00:00:00:000')
SET @Retries = 0
WHILE @Retries <= 100
BEGIN
SET @Retries = @Retries + 1 -- Increment loop counter and retry
SET @StartTime = getdate()
WAITFOR DELAY @MillisecondDelayTime
SET @EndTime = getdate()
INSERT INTO #Metrics VALUES(@MillisecondDelay,
@MillisecondDelayTime, @Retries, @StartTime, @EndTime)
END
END

SELECT
MillisecondDelayTime,
AVG(ActualMilliseconds) AS ActualAverageMilliseconds
FROM (
SELECT
MillisecondDelayTime,
DATEDIFF(ms, StartTime, EndTime) AS ActualMilliseconds
FROM #Metrics a
WHERE Retry > 1) AS Metrics
GROUP BY
MillisecondDelayTime
ORDER BY
MillisecondDelayTime

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Aaron W. West" <tallpeak@hotmail.NO.SPAM> wrote in message
news:E7SdnXfpDs-nl3TdRVn-vw@speakeasy.net...[color=blue]
> I also notice after running a few tests that WAITFOR seems to have a
> resolution of 1/100th second.
>
> Specifying a delay of '00:00:00:003' causes the same delay as if I'd
> specified 1/100th second.
>
> WAITFOR DELAY '00:00:00:013' causes a 2/100th second delay (:020 or .02),[/color]
so[color=blue]
> it apparently always rounds up.
>
> The behavior seems easier to follow with less output.
>
>
> alter PROCEDURE [sp_TestDelay] AS
> DECLARE @DELAYPERIOD DATETIME
> SET @DELAYPERIOD = '00:00:00:013'
> DECLARE @Retries INT
> SET @Retries = 0
> WHILE ( @Retries <= 1000 ) BEGIN
> PRINT DATEPART( MILLISECOND, getdate() )
> SET @Retries = @Retries + 1 -- Increment loop counter and retry
> WAITFOR DELAY @DELAYPERIOD
> END
> GO
>
> exec sp_testdelay
>
> 577
> 597
> 617
> 637
> 657
> ... etc.
>
>
> "Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
> news:rS4Gc.5251$R36.1439@newsread2.news.pas.earthl ink.net...
> The SQL Server datetime datatype, and therefore WAITFOR DELAY, is only
> accurate to 3.33 milliseconds and rounding may occur. You can see what is
> happening with the following
>
> DECLARE @DELAYPERIOD datetime
> SET @DELAYPERIOD = '00:00:00:001'
> SELECT @DELAYPERIOD
>
> This is documented in the SQL Server 2000 Books Online:
>
> <Excerpt href="tsqlref.chm::/ts_da-db_9xut.htm">
>
> Date and time data from January 1, 1753 through December 31, 9999, to an
> accuracy of one three-hundredth of a second (equivalent to 3.33[/color]
milliseconds[color=blue]
> or 0.00333 seconds). Values are rounded to increments of .000, .003, or[/color]
..007[color=blue]
> seconds, as shown in the table.
>
> Example Rounded example
> 01/01/98 23:59:59.999 1998-01-02 00:00:00.000
>
> 01/01/98 23:59:59.995, 1998-01-01 23:59:59.997
> 01/01/98 23:59:59.996,
> 01/01/98 23:59:59.997, or
> 01/01/98 23:59:59.998
>
> 01/01/98 23:59:59.992, 1998-01-01 23:59:59.993
> 01/01/98 23:59:59.993,
> 01/01/98 23:59:59.994
>
> 01/01/98 23:59:59.990 or 1998-01-01 23:59:59.990
> 01/01/98 23:59:59.991
>
> </Excerpt>
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Des L. Davis" <des.davis@cogeco.ca> wrote in message
> news:yq3Gc.34099$XY6.2568035@read2.cgocable.net...[color=green]
> > System: Dell PowerEdge Server with 3 GB RAM, 2.4 GHz Celeron
> > Software: Microsoft SQL Server 2000 Enterprise running on Windows 2003
> > Server
> > Software: Microsoft SQL Server 2000 Enterprise running on Windows 2000
> > Server
> >
> > If you run the code below, you'll notice something odd occuring. The
> > MilliSecond value does not change after a 1Millisecond delay. Is this a[/color]
> bug[color=green]
> > or am I doing something wrong?
> > Any assistance will be greatly appreciated[/color]
>
> --------------------------------------------------------------------------
> --[color=green]
> > -------
> > /*
> > Programmer : Des L. Davis
> > Date : July 4, 2004
> > Purpose : Testing Delayed Reaction
> > */
> >
> > CREATE PROCEDURE [sp_TestDelay] AS
> >
> > DECLARE @DELAYPERIOD NCHAR(12)
> > SET @DELAYPERIOD = '00:00:00:001'
> > DECLARE @Retries INT
> > SET @Retries = 0
> > DECLARE @MAXTRIES INT
> > SET @MAXTRIES = 1000 -- Maximum number of tries before timing out
> > DECLARE @Modified DATETIME
> >
> > WHILE ( ( @Retries <= @MAXTRIES ) ) BEGIN
> >
> > SET @Modified = GetDate() -- Set Modification Date
> >
> > PRINT @Retries
> > PRINT @Modified
> > PRINT 'Year = ' + CONVERT( NCHAR(4), DATEPART( YEAR, @Modified ) )
> > PRINT 'DayOfYear = ' + CONVERT( NCHAR(3), DATEPART( DAYOFYEAR ,
> > @Modified ) )
> > PRINT 'Hour = ' + CONVERT( NCHAR(2), DATEPART( HOUR, @Modified ) )
> > PRINT 'Minute = ' + CONVERT( NCHAR(2), DATEPART( MINUTE,[/color]
> @Modified ) )[color=green]
> > PRINT 'Second = ' + CONVERT( NCHAR(2), DATEPART( SECOND,[/color][/color]
@Modified ) )[color=blue][color=green]
> > PRINT 'MilliSecond = ' + CONVERT( NCHAR(3), DATEPART( MILLISECOND,
> > @Modified ) )
> >
> > SET @Retries = @Retries + 1 -- Increment loop counter and retry
> >
> > WAITFOR DELAY @DELAYPERIOD
> >
> > END
> > GO
> >[/color]
>
> --------------------------------------------------------------------------
> --[color=green]
> > -------
> >
> >
> >[/color]
>
>
>[/color]

re: WAITFOR DELAY

Guys,

I appreciate the assistance you've all provided. Any suggested work around?

"Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
news:MhgGc.5715$R36.1090@newsread2.news.pas.earthl ink.net...[color=blue]
> Standard Windows time API calls are only accurate to about 10 ms and this[/color]
is[color=blue]
> apparently how SQL Server implements WAITFOR DELAY. When I run a Profiler
> trace, I see statements that run quickly have a reported duration of[/color]
either[color=blue]
> 0 , 13 or 16 milliseconds but nothing in between.
>
> Below is my test script that shows the actual behavior of WAITFOR DELAY.
> The bottom line is that SQL Server will wait for at least the specified[/color]
time[color=blue]
> but can wait longer due to the combination of datetime rounding and timer
> interval resolution.
>
> SET NOCOUNT ON
> IF OBJECT_ID('tempdb..#Metrics') IS NOT NULL DROP TABLE #Metrics
> CREATE TABLE #Metrics
> (
> MillisecondDelay int NOT NULL,
> MillisecondDelayTime datetime NOT NULL,
> Retry int NOT NULL,
> StartTime datetime NOT NULL,
> EndTime datetime NOT NULL
> )
> DECLARE @MillisecondDelay int
> DECLARE @MillisecondDelayTime datetime
> DECLARE @Retries int
> DECLARE @StartTime datetime
> DECLARE @EndTime datetime
>
> SET @MillisecondDelay = 0
> WHILE @MillisecondDelay < 50
> BEGIN
> SET @MillisecondDelay = @MillisecondDelay + 1
> SET @MillisecondDelayTime = DATEADD(ms, @MillisecondDelay,
> '00:00:00:000')
> SET @Retries = 0
> WHILE @Retries <= 100
> BEGIN
> SET @Retries = @Retries + 1 -- Increment loop counter and retry
> SET @StartTime = getdate()
> WAITFOR DELAY @MillisecondDelayTime
> SET @EndTime = getdate()
> INSERT INTO #Metrics VALUES(@MillisecondDelay,
> @MillisecondDelayTime, @Retries, @StartTime, @EndTime)
> END
> END
>
> SELECT
> MillisecondDelayTime,
> AVG(ActualMilliseconds) AS ActualAverageMilliseconds
> FROM (
> SELECT
> MillisecondDelayTime,
> DATEDIFF(ms, StartTime, EndTime) AS ActualMilliseconds
> FROM #Metrics a
> WHERE Retry > 1) AS Metrics
> GROUP BY
> MillisecondDelayTime
> ORDER BY
> MillisecondDelayTime
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Aaron W. West" <tallpeak@hotmail.NO.SPAM> wrote in message
> news:E7SdnXfpDs-nl3TdRVn-vw@speakeasy.net...[color=green]
> > I also notice after running a few tests that WAITFOR seems to have a
> > resolution of 1/100th second.
> >
> > Specifying a delay of '00:00:00:003' causes the same delay as if I'd
> > specified 1/100th second.
> >
> > WAITFOR DELAY '00:00:00:013' causes a 2/100th second delay (:020 or[/color][/color]
..02),[color=blue]
> so[color=green]
> > it apparently always rounds up.
> >
> > The behavior seems easier to follow with less output.
> >
> >
> > alter PROCEDURE [sp_TestDelay] AS
> > DECLARE @DELAYPERIOD DATETIME
> > SET @DELAYPERIOD = '00:00:00:013'
> > DECLARE @Retries INT
> > SET @Retries = 0
> > WHILE ( @Retries <= 1000 ) BEGIN
> > PRINT DATEPART( MILLISECOND, getdate() )
> > SET @Retries = @Retries + 1 -- Increment loop counter and retry
> > WAITFOR DELAY @DELAYPERIOD
> > END
> > GO
> >
> > exec sp_testdelay
> >
> > 577
> > 597
> > 617
> > 637
> > 657
> > ... etc.
> >
> >
> > "Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
> > news:rS4Gc.5251$R36.1439@newsread2.news.pas.earthl ink.net...
> > The SQL Server datetime datatype, and therefore WAITFOR DELAY, is only
> > accurate to 3.33 milliseconds and rounding may occur. You can see what[/color][/color]
is[color=blue][color=green]
> > happening with the following
> >
> > DECLARE @DELAYPERIOD datetime
> > SET @DELAYPERIOD = '00:00:00:001'
> > SELECT @DELAYPERIOD
> >
> > This is documented in the SQL Server 2000 Books Online:
> >
> > <Excerpt href="tsqlref.chm::/ts_da-db_9xut.htm">
> >
> > Date and time data from January 1, 1753 through December 31, 9999, to an
> > accuracy of one three-hundredth of a second (equivalent to 3.33[/color]
> milliseconds[color=green]
> > or 0.00333 seconds). Values are rounded to increments of .000, .003, or[/color]
> .007[color=green]
> > seconds, as shown in the table.
> >
> > Example Rounded example
> > 01/01/98 23:59:59.999 1998-01-02 00:00:00.000
> >
> > 01/01/98 23:59:59.995, 1998-01-01 23:59:59.997
> > 01/01/98 23:59:59.996,
> > 01/01/98 23:59:59.997, or
> > 01/01/98 23:59:59.998
> >
> > 01/01/98 23:59:59.992, 1998-01-01 23:59:59.993
> > 01/01/98 23:59:59.993,
> > 01/01/98 23:59:59.994
> >
> > 01/01/98 23:59:59.990 or 1998-01-01 23:59:59.990
> > 01/01/98 23:59:59.991
> >
> > </Excerpt>
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Des L. Davis" <des.davis@cogeco.ca> wrote in message
> > news:yq3Gc.34099$XY6.2568035@read2.cgocable.net...[color=darkred]
> > > System: Dell PowerEdge Server with 3 GB RAM, 2.4 GHz Celeron
> > > Software: Microsoft SQL Server 2000 Enterprise running on Windows 2003
> > > Server
> > > Software: Microsoft SQL Server 2000 Enterprise running on Windows 2000
> > > Server
> > >
> > > If you run the code below, you'll notice something odd occuring. The
> > > MilliSecond value does not change after a 1Millisecond delay. Is this[/color][/color][/color]
a[color=blue][color=green]
> > bug[color=darkred]
> > > or am I doing something wrong?
> > > Any assistance will be greatly appreciated[/color]
> >[/color]
>
> --------------------------------------------------------------------------[color=green]
> > --[color=darkred]
> > > -------
> > > /*
> > > Programmer : Des L. Davis
> > > Date : July 4, 2004
> > > Purpose : Testing Delayed Reaction
> > > */
> > >
> > > CREATE PROCEDURE [sp_TestDelay] AS
> > >
> > > DECLARE @DELAYPERIOD NCHAR(12)
> > > SET @DELAYPERIOD = '00:00:00:001'
> > > DECLARE @Retries INT
> > > SET @Retries = 0
> > > DECLARE @MAXTRIES INT
> > > SET @MAXTRIES = 1000 -- Maximum number of tries before timing out
> > > DECLARE @Modified DATETIME
> > >
> > > WHILE ( ( @Retries <= @MAXTRIES ) ) BEGIN
> > >
> > > SET @Modified = GetDate() -- Set Modification Date
> > >
> > > PRINT @Retries
> > > PRINT @Modified
> > > PRINT 'Year = ' + CONVERT( NCHAR(4), DATEPART( YEAR,[/color][/color][/color]
@Modified ) )[color=blue][color=green][color=darkred]
> > > PRINT 'DayOfYear = ' + CONVERT( NCHAR(3), DATEPART( DAYOFYEAR ,
> > > @Modified ) )
> > > PRINT 'Hour = ' + CONVERT( NCHAR(2), DATEPART( HOUR,[/color][/color][/color]
@Modified ) )[color=blue][color=green][color=darkred]
> > > PRINT 'Minute = ' + CONVERT( NCHAR(2), DATEPART( MINUTE,[/color]
> > @Modified ) )[color=darkred]
> > > PRINT 'Second = ' + CONVERT( NCHAR(2), DATEPART( SECOND,[/color][/color]
> @Modified ) )[color=green][color=darkred]
> > > PRINT 'MilliSecond = ' + CONVERT( NCHAR(3), DATEPART([/color][/color][/color]
MILLISECOND,[color=blue][color=green][color=darkred]
> > > @Modified ) )
> > >
> > > SET @Retries = @Retries + 1 -- Increment loop counter and retry
> > >
> > > WAITFOR DELAY @DELAYPERIOD
> > >
> > > END
> > > GO
> > >[/color]
> >[/color]
>
> --------------------------------------------------------------------------[color=green]
> > --[color=darkred]
> > > -------
> > >
> > >
> > >[/color]
> >
> >
> >[/color]
>
>[/color]

re: WAITFOR DELAY

Des L. Davis (des.davis@cogeco.ca) writes:[color=blue]
> I appreciate the assistance you've all provided. Any suggested work
> around?[/color]

Maybe. If you tell us what the actual business problem you are trying
to solve, we might come up with something. Trying to run a waitfor in
only 1 ms, when there is a 10 ms accuracy sounds like a dead end to me.

OK, so you could write an extended stored procedure to do the waiting,
but if the Windows API only gives you 10 ms, you would have to roll your
own, which does not sound trivial to me.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

re: WAITFOR DELAY

Erland's extended proc idea is interesting but, in addition the complexity,
there may be some overhead involved. Perhaps you can describe the business
problem. Perhaps there are better methods besides SQL.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Des L. Davis" <des.davis@cogeco.ca> wrote in message
news:bHiGc.36740$_V4.19756@read1.cgocable.net...[color=blue]
> Guys,
>
> I appreciate the assistance you've all provided. Any suggested work[/color]
around?[color=blue]
>
> "Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
> news:MhgGc.5715$R36.1090@newsread2.news.pas.earthl ink.net...[color=green]
> > Standard Windows time API calls are only accurate to about 10 ms and[/color][/color]
this[color=blue]
> is[color=green]
> > apparently how SQL Server implements WAITFOR DELAY. When I run a[/color][/color]
Profiler[color=blue][color=green]
> > trace, I see statements that run quickly have a reported duration of[/color]
> either[color=green]
> > 0 , 13 or 16 milliseconds but nothing in between.
> >
> > Below is my test script that shows the actual behavior of WAITFOR DELAY.
> > The bottom line is that SQL Server will wait for at least the specified[/color]
> time[color=green]
> > but can wait longer due to the combination of datetime rounding and[/color][/color]
timer[color=blue][color=green]
> > interval resolution.
> >
> > SET NOCOUNT ON
> > IF OBJECT_ID('tempdb..#Metrics') IS NOT NULL DROP TABLE #Metrics
> > CREATE TABLE #Metrics
> > (
> > MillisecondDelay int NOT NULL,
> > MillisecondDelayTime datetime NOT NULL,
> > Retry int NOT NULL,
> > StartTime datetime NOT NULL,
> > EndTime datetime NOT NULL
> > )
> > DECLARE @MillisecondDelay int
> > DECLARE @MillisecondDelayTime datetime
> > DECLARE @Retries int
> > DECLARE @StartTime datetime
> > DECLARE @EndTime datetime
> >
> > SET @MillisecondDelay = 0
> > WHILE @MillisecondDelay < 50
> > BEGIN
> > SET @MillisecondDelay = @MillisecondDelay + 1
> > SET @MillisecondDelayTime = DATEADD(ms, @MillisecondDelay,
> > '00:00:00:000')
> > SET @Retries = 0
> > WHILE @Retries <= 100
> > BEGIN
> > SET @Retries = @Retries + 1 -- Increment loop counter and[/color][/color]
retry[color=blue][color=green]
> > SET @StartTime = getdate()
> > WAITFOR DELAY @MillisecondDelayTime
> > SET @EndTime = getdate()
> > INSERT INTO #Metrics VALUES(@MillisecondDelay,
> > @MillisecondDelayTime, @Retries, @StartTime, @EndTime)
> > END
> > END
> >
> > SELECT
> > MillisecondDelayTime,
> > AVG(ActualMilliseconds) AS ActualAverageMilliseconds
> > FROM (
> > SELECT
> > MillisecondDelayTime,
> > DATEDIFF(ms, StartTime, EndTime) AS ActualMilliseconds
> > FROM #Metrics a
> > WHERE Retry > 1) AS Metrics
> > GROUP BY
> > MillisecondDelayTime
> > ORDER BY
> > MillisecondDelayTime
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Aaron W. West" <tallpeak@hotmail.NO.SPAM> wrote in message
> > news:E7SdnXfpDs-nl3TdRVn-vw@speakeasy.net...[color=darkred]
> > > I also notice after running a few tests that WAITFOR seems to have a
> > > resolution of 1/100th second.
> > >
> > > Specifying a delay of '00:00:00:003' causes the same delay as if I'd
> > > specified 1/100th second.
> > >
> > > WAITFOR DELAY '00:00:00:013' causes a 2/100th second delay (:020 or[/color][/color]
> .02),[color=green]
> > so[color=darkred]
> > > it apparently always rounds up.
> > >
> > > The behavior seems easier to follow with less output.
> > >
> > >
> > > alter PROCEDURE [sp_TestDelay] AS
> > > DECLARE @DELAYPERIOD DATETIME
> > > SET @DELAYPERIOD = '00:00:00:013'
> > > DECLARE @Retries INT
> > > SET @Retries = 0
> > > WHILE ( @Retries <= 1000 ) BEGIN
> > > PRINT DATEPART( MILLISECOND, getdate() )
> > > SET @Retries = @Retries + 1 -- Increment loop counter and retry
> > > WAITFOR DELAY @DELAYPERIOD
> > > END
> > > GO
> > >
> > > exec sp_testdelay
> > >
> > > 577
> > > 597
> > > 617
> > > 637
> > > 657
> > > ... etc.
> > >
> > >
> > > "Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
> > > news:rS4Gc.5251$R36.1439@newsread2.news.pas.earthl ink.net...
> > > The SQL Server datetime datatype, and therefore WAITFOR DELAY, is only
> > > accurate to 3.33 milliseconds and rounding may occur. You can see[/color][/color][/color]
what[color=blue]
> is[color=green][color=darkred]
> > > happening with the following
> > >
> > > DECLARE @DELAYPERIOD datetime
> > > SET @DELAYPERIOD = '00:00:00:001'
> > > SELECT @DELAYPERIOD
> > >
> > > This is documented in the SQL Server 2000 Books Online:
> > >
> > > <Excerpt href="tsqlref.chm::/ts_da-db_9xut.htm">
> > >
> > > Date and time data from January 1, 1753 through December 31, 9999, to[/color][/color][/color]
an[color=blue][color=green][color=darkred]
> > > accuracy of one three-hundredth of a second (equivalent to 3.33[/color]
> > milliseconds[color=darkred]
> > > or 0.00333 seconds). Values are rounded to increments of .000, .003,[/color][/color][/color]
or[color=blue][color=green]
> > .007[color=darkred]
> > > seconds, as shown in the table.
> > >
> > > Example Rounded example
> > > 01/01/98 23:59:59.999 1998-01-02 00:00:00.000
> > >
> > > 01/01/98 23:59:59.995, 1998-01-01 23:59:59.997
> > > 01/01/98 23:59:59.996,
> > > 01/01/98 23:59:59.997, or
> > > 01/01/98 23:59:59.998
> > >
> > > 01/01/98 23:59:59.992, 1998-01-01 23:59:59.993
> > > 01/01/98 23:59:59.993,
> > > 01/01/98 23:59:59.994
> > >
> > > 01/01/98 23:59:59.990 or 1998-01-01 23:59:59.990
> > > 01/01/98 23:59:59.991
> > >
> > > </Excerpt>
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > "Des L. Davis" <des.davis@cogeco.ca> wrote in message
> > > news:yq3Gc.34099$XY6.2568035@read2.cgocable.net...
> > > > System: Dell PowerEdge Server with 3 GB RAM, 2.4 GHz Celeron
> > > > Software: Microsoft SQL Server 2000 Enterprise running on Windows[/color][/color][/color]
2003[color=blue][color=green][color=darkred]
> > > > Server
> > > > Software: Microsoft SQL Server 2000 Enterprise running on Windows[/color][/color][/color]
2000[color=blue][color=green][color=darkred]
> > > > Server
> > > >
> > > > If you run the code below, you'll notice something odd occuring.[/color][/color][/color]
The[color=blue][color=green][color=darkred]
> > > > MilliSecond value does not change after a 1Millisecond delay. Is[/color][/color][/color]
this[color=blue]
> a[color=green][color=darkred]
> > > bug
> > > > or am I doing something wrong?
> > > > Any assistance will be greatly appreciated
> > >[/color]
> >[/color]
>
> --------------------------------------------------------------------------[color=green][color=darkred]
> > > --
> > > > -------
> > > > /*
> > > > Programmer : Des L. Davis
> > > > Date : July 4, 2004
> > > > Purpose : Testing Delayed Reaction
> > > > */
> > > >
> > > > CREATE PROCEDURE [sp_TestDelay] AS
> > > >
> > > > DECLARE @DELAYPERIOD NCHAR(12)
> > > > SET @DELAYPERIOD = '00:00:00:001'
> > > > DECLARE @Retries INT
> > > > SET @Retries = 0
> > > > DECLARE @MAXTRIES INT
> > > > SET @MAXTRIES = 1000 -- Maximum number of tries before timing out
> > > > DECLARE @Modified DATETIME
> > > >
> > > > WHILE ( ( @Retries <= @MAXTRIES ) ) BEGIN
> > > >
> > > > SET @Modified = GetDate() -- Set Modification Date
> > > >
> > > > PRINT @Retries
> > > > PRINT @Modified
> > > > PRINT 'Year = ' + CONVERT( NCHAR(4), DATEPART( YEAR,[/color][/color]
> @Modified ) )[color=green][color=darkred]
> > > > PRINT 'DayOfYear = ' + CONVERT( NCHAR(3), DATEPART( DAYOFYEAR ,
> > > > @Modified ) )
> > > > PRINT 'Hour = ' + CONVERT( NCHAR(2), DATEPART( HOUR,[/color][/color]
> @Modified ) )[color=green][color=darkred]
> > > > PRINT 'Minute = ' + CONVERT( NCHAR(2), DATEPART( MINUTE,
> > > @Modified ) )
> > > > PRINT 'Second = ' + CONVERT( NCHAR(2), DATEPART( SECOND,[/color]
> > @Modified ) )[color=darkred]
> > > > PRINT 'MilliSecond = ' + CONVERT( NCHAR(3), DATEPART([/color][/color]
> MILLISECOND,[color=green][color=darkred]
> > > > @Modified ) )
> > > >
> > > > SET @Retries = @Retries + 1 -- Increment loop counter and[/color][/color][/color]
retry[color=blue][color=green][color=darkred]
> > > >
> > > > WAITFOR DELAY @DELAYPERIOD
> > > >
> > > > END
> > > > GO
> > > >
> > >[/color]
> >[/color]
>
> --------------------------------------------------------------------------[color=green][color=darkred]
> > > --
> > > > -------
> > > >
> > > >
> > > >
> > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

re: WAITFOR DELAY

"Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
news:MjmGc.6046$R36.5921@newsread2.news.pas.earthl ink.net...[color=blue]
> Erland's extended proc idea is interesting but, in addition the[/color]
complexity,[color=blue]
> there may be some overhead involved. Perhaps you can describe the[/color]
business[color=blue]
> problem. Perhaps there are better methods besides SQL.[/color]

Btw, I want to mention the only production use I've seen of this (WAITFOR)
on our servers is I think bad programming. (i.e. it solved the symptom, but
I'm not sure it solved the underlying problem.)

re: WAITFOR DELAY

Here is the scenario:

I have several hundred thousand rows of data in a MS Access database that
must be imported into SQL Server. I cannot employ a simple import since the
underlying table schema in both databases are different. So I wrote a C#
import routine to do the dirty work for me. The C# application - which
we'll call ImportData.EXE - interacts with a SQL Server 2000 Enterprise
server database through stored procs only. One of the procs -
sp_SaveImportedData - is responsible for saving the imported data. As part
of it's insert routine sp_SaveImportedData generates a timebased PrimaryKey
(see the formula below) and tries to insert the data using that PrimaryKey.
If the insertion fails because of a duplicate PK (i.e. @@ERROR = 2627)
sp_SaveImportedData is instructed to wait 1 millisecond (using WAITFOR
DELAY '00:00:00:001'), generate a new PK, and retry the insert operation.
This happens in a loop which is aborted after 1000 attempts. Any errors are
written to a text file for later reconcilliation.

If it all works, the PK should look something like 2004186773134305 which is
the result of the following concatenation

PK = dbo. PadLeft ( @Year, '0', 4 ) + dbo.PadLeft ( @DayOfYear, '0', 3 )
+ dbo. PadLeft ( @MilliSecond, '0', 3 ) + dbo.PadLeft ( @Hour, '0', 2 )
+ dbo.PadLeft ( @Minute, '0', 2 ) + dbo.PadLeft ( @Second, '0', 2 )

where PadLeft is a UDF

This allows me to insert a theoritical maximum of 1000 rows of data into the
SQL Server every second. Obviously this isn't occuring in practice. BTW
this is a non issue on an older system (PIII 667, 1GB) - everything works
flawlessly which is why the issue went un-noticed for several weeks.

The obvious solution here is to re-work how the keys are generated but I'm
attempting to better understand what are my real options.

"Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
news:MjmGc.6046$R36.5921@newsread2.news.pas.earthl ink.net...[color=blue]
> Erland's extended proc idea is interesting but, in addition the complexity[/color]
,[color=blue]
> there may be some overhead involved. Perhaps you can describe the[/color]
business[color=blue]
> problem. Perhaps there are better methods besides SQL.
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Des L. Davis" <des.davis@cogeco.ca> wrote in message
> news:bHiGc.36740$_V4.19756@read1.cgocable.net...[color=green]
> > Guys,
> >
> > I appreciate the assistance you've all provided. Any suggested work[/color]
> around?[color=green]
> >
> > "Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
> > news:MhgGc.5715$R36.1090@newsread2.news.pas.earthl ink.net...[color=darkred]
> > > Standard Windows time API calls are only accurate to about 10 ms and[/color][/color]
> this[color=green]
> > is[color=darkred]
> > > apparently how SQL Server implements WAITFOR DELAY. When I run a[/color][/color]
> Profiler[color=green][color=darkred]
> > > trace, I see statements that run quickly have a reported duration of[/color]
> > either[color=darkred]
> > > 0 , 13 or 16 milliseconds but nothing in between.
> > >
> > > Below is my test script that shows the actual behavior of WAITFOR[/color][/color][/color]
DELAY.[color=blue][color=green][color=darkred]
> > > The bottom line is that SQL Server will wait for at least the[/color][/color][/color]
specified[color=blue][color=green]
> > time[color=darkred]
> > > but can wait longer due to the combination of datetime rounding and[/color][/color]
> timer[color=green][color=darkred]
> > > interval resolution.
> > >
> > > SET NOCOUNT ON
> > > IF OBJECT_ID('tempdb..#Metrics') IS NOT NULL DROP TABLE #Metrics
> > > CREATE TABLE #Metrics
> > > (
> > > MillisecondDelay int NOT NULL,
> > > MillisecondDelayTime datetime NOT NULL,
> > > Retry int NOT NULL,
> > > StartTime datetime NOT NULL,
> > > EndTime datetime NOT NULL
> > > )
> > > DECLARE @MillisecondDelay int
> > > DECLARE @MillisecondDelayTime datetime
> > > DECLARE @Retries int
> > > DECLARE @StartTime datetime
> > > DECLARE @EndTime datetime
> > >
> > > SET @MillisecondDelay = 0
> > > WHILE @MillisecondDelay < 50
> > > BEGIN
> > > SET @MillisecondDelay = @MillisecondDelay + 1
> > > SET @MillisecondDelayTime = DATEADD(ms, @MillisecondDelay,
> > > '00:00:00:000')
> > > SET @Retries = 0
> > > WHILE @Retries <= 100
> > > BEGIN
> > > SET @Retries = @Retries + 1 -- Increment loop counter and[/color][/color]
> retry[color=green][color=darkred]
> > > SET @StartTime = getdate()
> > > WAITFOR DELAY @MillisecondDelayTime
> > > SET @EndTime = getdate()
> > > INSERT INTO #Metrics VALUES(@MillisecondDelay,
> > > @MillisecondDelayTime, @Retries, @StartTime, @EndTime)
> > > END
> > > END
> > >
> > > SELECT
> > > MillisecondDelayTime,
> > > AVG(ActualMilliseconds) AS ActualAverageMilliseconds
> > > FROM (
> > > SELECT
> > > MillisecondDelayTime,
> > > DATEDIFF(ms, StartTime, EndTime) AS ActualMilliseconds
> > > FROM #Metrics a
> > > WHERE Retry > 1) AS Metrics
> > > GROUP BY
> > > MillisecondDelayTime
> > > ORDER BY
> > > MillisecondDelayTime
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > "Aaron W. West" <tallpeak@hotmail.NO.SPAM> wrote in message
> > > news:E7SdnXfpDs-nl3TdRVn-vw@speakeasy.net...
> > > > I also notice after running a few tests that WAITFOR seems to have a
> > > > resolution of 1/100th second.
> > > >
> > > > Specifying a delay of '00:00:00:003' causes the same delay as if I'd
> > > > specified 1/100th second.
> > > >
> > > > WAITFOR DELAY '00:00:00:013' causes a 2/100th second delay (:020 or[/color]
> > .02),[color=darkred]
> > > so
> > > > it apparently always rounds up.
> > > >
> > > > The behavior seems easier to follow with less output.
> > > >
> > > >
> > > > alter PROCEDURE [sp_TestDelay] AS
> > > > DECLARE @DELAYPERIOD DATETIME
> > > > SET @DELAYPERIOD = '00:00:00:013'
> > > > DECLARE @Retries INT
> > > > SET @Retries = 0
> > > > WHILE ( @Retries <= 1000 ) BEGIN
> > > > PRINT DATEPART( MILLISECOND, getdate() )
> > > > SET @Retries = @Retries + 1 -- Increment loop counter and[/color][/color][/color]
retry[color=blue][color=green][color=darkred]
> > > > WAITFOR DELAY @DELAYPERIOD
> > > > END
> > > > GO
> > > >
> > > > exec sp_testdelay
> > > >
> > > > 577
> > > > 597
> > > > 617
> > > > 637
> > > > 657
> > > > ... etc.
> > > >
> > > >
> > > > "Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
> > > > news:rS4Gc.5251$R36.1439@newsread2.news.pas.earthl ink.net...
> > > > The SQL Server datetime datatype, and therefore WAITFOR DELAY, is[/color][/color][/color]
only[color=blue][color=green][color=darkred]
> > > > accurate to 3.33 milliseconds and rounding may occur. You can see[/color][/color]
> what[color=green]
> > is[color=darkred]
> > > > happening with the following
> > > >
> > > > DECLARE @DELAYPERIOD datetime
> > > > SET @DELAYPERIOD = '00:00:00:001'
> > > > SELECT @DELAYPERIOD
> > > >
> > > > This is documented in the SQL Server 2000 Books Online:
> > > >
> > > > <Excerpt href="tsqlref.chm::/ts_da-db_9xut.htm">
> > > >
> > > > Date and time data from January 1, 1753 through December 31, 9999,[/color][/color][/color]
to[color=blue]
> an[color=green][color=darkred]
> > > > accuracy of one three-hundredth of a second (equivalent to 3.33
> > > milliseconds
> > > > or 0.00333 seconds). Values are rounded to increments of .000, .003,[/color][/color]
> or[color=green][color=darkred]
> > > .007
> > > > seconds, as shown in the table.
> > > >
> > > > Example Rounded example
> > > > 01/01/98 23:59:59.999 1998-01-02 00:00:00.000
> > > >
> > > > 01/01/98 23:59:59.995, 1998-01-01 23:59:59.997
> > > > 01/01/98 23:59:59.996,
> > > > 01/01/98 23:59:59.997, or
> > > > 01/01/98 23:59:59.998
> > > >
> > > > 01/01/98 23:59:59.992, 1998-01-01 23:59:59.993
> > > > 01/01/98 23:59:59.993,
> > > > 01/01/98 23:59:59.994
> > > >
> > > > 01/01/98 23:59:59.990 or 1998-01-01 23:59:59.990
> > > > 01/01/98 23:59:59.991
> > > >
> > > > </Excerpt>
> > > >
> > > > --
> > > > Hope this helps.
> > > >
> > > > Dan Guzman
> > > > SQL Server MVP
> > > >
> > > > "Des L. Davis" <des.davis@cogeco.ca> wrote in message
> > > > news:yq3Gc.34099$XY6.2568035@read2.cgocable.net...
> > > > > System: Dell PowerEdge Server with 3 GB RAM, 2.4 GHz Celeron
> > > > > Software: Microsoft SQL Server 2000 Enterprise running on Windows[/color][/color]
> 2003[color=green][color=darkred]
> > > > > Server
> > > > > Software: Microsoft SQL Server 2000 Enterprise running on Windows[/color][/color]
> 2000[color=green][color=darkred]
> > > > > Server
> > > > >
> > > > > If you run the code below, you'll notice something odd occuring.[/color][/color]
> The[color=green][color=darkred]
> > > > > MilliSecond value does not change after a 1Millisecond delay. Is[/color][/color]
> this[color=green]
> > a[color=darkred]
> > > > bug
> > > > > or am I doing something wrong?
> > > > > Any assistance will be greatly appreciated
> > > >
> > >[/color]
> >[/color]
>
> --------------------------------------------------------------------------[color=green][color=darkred]
> > > > --
> > > > > -------
> > > > > /*
> > > > > Programmer : Des L. Davis
> > > > > Date : July 4, 2004
> > > > > Purpose : Testing Delayed Reaction
> > > > > */
> > > > >
> > > > > CREATE PROCEDURE [sp_TestDelay] AS
> > > > >
> > > > > DECLARE @DELAYPERIOD NCHAR(12)
> > > > > SET @DELAYPERIOD = '00:00:00:001'
> > > > > DECLARE @Retries INT
> > > > > SET @Retries = 0
> > > > > DECLARE @MAXTRIES INT
> > > > > SET @MAXTRIES = 1000 -- Maximum number of tries before timing[/color][/color][/color]
out[color=blue][color=green][color=darkred]
> > > > > DECLARE @Modified DATETIME
> > > > >
> > > > > WHILE ( ( @Retries <= @MAXTRIES ) ) BEGIN
> > > > >
> > > > > SET @Modified = GetDate() -- Set Modification Date
> > > > >
> > > > > PRINT @Retries
> > > > > PRINT @Modified
> > > > > PRINT 'Year = ' + CONVERT( NCHAR(4), DATEPART( YEAR,[/color]
> > @Modified ) )[color=darkred]
> > > > > PRINT 'DayOfYear = ' + CONVERT( NCHAR(3), DATEPART( DAYOFYEAR[/color][/color][/color]
,[color=blue][color=green][color=darkred]
> > > > > @Modified ) )
> > > > > PRINT 'Hour = ' + CONVERT( NCHAR(2), DATEPART( HOUR,[/color]
> > @Modified ) )[color=darkred]
> > > > > PRINT 'Minute = ' + CONVERT( NCHAR(2), DATEPART( MINUTE,
> > > > @Modified ) )
> > > > > PRINT 'Second = ' + CONVERT( NCHAR(2), DATEPART( SECOND,
> > > @Modified ) )
> > > > > PRINT 'MilliSecond = ' + CONVERT( NCHAR(3), DATEPART([/color]
> > MILLISECOND,[color=darkred]
> > > > > @Modified ) )
> > > > >
> > > > > SET @Retries = @Retries + 1 -- Increment loop counter and[/color][/color]
> retry[color=green][color=darkred]
> > > > >
> > > > > WAITFOR DELAY @DELAYPERIOD
> > > > >
> > > > > END
> > > > > GO
> > > > >
> > > >
> > >[/color]
> >[/color]
>
> --------------------------------------------------------------------------[color=green][color=darkred]
> > > > --
> > > > > -------
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

re: WAITFOR DELAY

Des L. Davis (des.davis@cogeco.ca) writes:[color=blue]
> I have several hundred thousand rows of data in a MS Access database
> that must be imported into SQL Server. I cannot employ a simple import
> since the underlying table schema in both databases are different. So I
> wrote a C# import routine to do the dirty work for me. The C#
> application - which we'll call ImportData.EXE - interacts with a SQL
> Server 2000 Enterprise server database through stored procs only. One
> of the procs - sp_SaveImportedData - is responsible for saving the
> imported data.[/color]

Standard comment: the sp_ prefix is reserved for system procedures and SQL
Server will first look for these in master.
[color=blue]
> If it all works, the PK should look something like 2004186773134305
> which is the result of the following concatenation
>
>PK = dbo. PadLeft ( @Year, '0', 4 ) + dbo.PadLeft ( @DayOfYear, '0', 3 )
>+ dbo. PadLeft ( @MilliSecond, '0', 3 ) + dbo.PadLeft ( @Hour, '0', 2 )
>+ dbo.PadLeft ( @Minute, '0', 2 ) + dbo.PadLeft ( @Second, '0', 2 )
>
> where PadLeft is a UDF
>
> This allows me to insert a theoritical maximum of 1000 rows of data into
> the SQL Server every second.[/color]

As you have learnt now, the theoretical maximum is 333 rows, and the actual
number probably even lower.
[color=blue]
> The obvious solution here is to re-work how the keys are generated but I'm
> attempting to better understand what are my real options.[/color]

It wasn't clear to me why have chosen this scheme and not a plain IDENTITY
column.

If you for some reason want the keys to be scattered, you could use a
uniqueidentifier column and assigned a value with newid().


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

re: WAITFOR DELAY

> The obvious solution here is to re-work how the keys are generated but I'm[color=blue]
> attempting to better understand what are my real options.[/color]

IMHO, you'll be better off generating a surrogate values using an different
algorithm. The IDENTITY approach Erland suggested will perform well,
guarantee uniqueness and handle concurrency. I'm interested in knowing why
you are deliberately assigning non-sequential values. More often than not,
'hot spots' are a good thing in SQL 7 and above due to row-level locking.

From your description, it appears there is a cursor loop involved somewhere,
although it's unclear to me whether this is in sp_SaveImportedData, another
proc or ImportData.EXE. In any case, if you want to keep your existing
algorithm, consider replacing the millisecond component with your own
sequence number that is initialized whenever the time component changes.
The untested Transact-SQL script below illustrates this technique.

DECLARE @DateComponent char(7)
DECLARE @TimeComponent char(6)
DECLARE @LastDateComponent char(7)
DECLARE @LastTimeComponent char(6)
DECLARE @PK char(16)
DECLARE @Sequence int

WHILE --some loop condition
BEGIN

SET @DateComponent =
dbo. PadLeft ( @Year, '0', 4 ) +
dbo.PadLeft ( @DayOfYear, '0', 3 )
SET @TimeComponent =
dbo.PadLeft ( @Hour, '0', 2 ) +
dbo.PadLeft ( @Minute, '0', 2 ) +
dbo.PadLeft ( @Second, '0', 2 )

IF @DateComponent + @TimeComponent <> @LastDateComponent +
@LastTimeComponent
BEGIN
SET @Sequence = 0
SET @LastDateComponent = @DateComponent
SET @LastTimeComponent = @TimeComponent
END
ELSE
BEGIN
SET @Sequence = @Sequence + 1
END
SET @PK =
@DateComponent +
dbo.PadLeft ( @Sequence, '0', 3 ) +
@TimeComponent

--other code here
END

--
Hope this helps.

Dan Guzman
SQL Server MVP

re: WAITFOR DELAY

I'm in the process of re-writing the key generating algorithm to eliminate
the WAITFOR DELAY. Thanks for all the valuable input.

Dan,

The keys appear non-sequential if you consider them numerical values.
However, they are sequential if you consider them time-based entities.


"Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
news:aMxGc.6914$oD3.707@newsread1.news.pas.earthli nk.net...[color=blue][color=green]
> > The obvious solution here is to re-work how the keys are generated but[/color][/color]
I'm[color=blue][color=green]
> > attempting to better understand what are my real options.[/color]
>
> IMHO, you'll be better off generating a surrogate values using an[/color]
different[color=blue]
> algorithm. The IDENTITY approach Erland suggested will perform well,
> guarantee uniqueness and handle concurrency. I'm interested in knowing[/color]
why[color=blue]
> you are deliberately assigning non-sequential values. More often than[/color]
not,[color=blue]
> 'hot spots' are a good thing in SQL 7 and above due to row-level locking.
>
> From your description, it appears there is a cursor loop involved[/color]
somewhere,[color=blue]
> although it's unclear to me whether this is in sp_SaveImportedData,[/color]
another[color=blue]
> proc or ImportData.EXE. In any case, if you want to keep your existing
> algorithm, consider replacing the millisecond component with your own
> sequence number that is initialized whenever the time component changes.
> The untested Transact-SQL script below illustrates this technique.
>
> DECLARE @DateComponent char(7)
> DECLARE @TimeComponent char(6)
> DECLARE @LastDateComponent char(7)
> DECLARE @LastTimeComponent char(6)
> DECLARE @PK char(16)
> DECLARE @Sequence int
>
> WHILE --some loop condition
> BEGIN
>
> SET @DateComponent =
> dbo. PadLeft ( @Year, '0', 4 ) +
> dbo.PadLeft ( @DayOfYear, '0', 3 )
> SET @TimeComponent =
> dbo.PadLeft ( @Hour, '0', 2 ) +
> dbo.PadLeft ( @Minute, '0', 2 ) +
> dbo.PadLeft ( @Second, '0', 2 )
>
> IF @DateComponent + @TimeComponent <> @LastDateComponent +
> @LastTimeComponent
> BEGIN
> SET @Sequence = 0
> SET @LastDateComponent = @DateComponent
> SET @LastTimeComponent = @TimeComponent
> END
> ELSE
> BEGIN
> SET @Sequence = @Sequence + 1
> END
> SET @PK =
> @DateComponent +
> dbo.PadLeft ( @Sequence, '0', 3 ) +
> @TimeComponent
>
> --other code here
> END
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
>[/color]

re: WAITFOR DELAY

> The keys appear non-sequential if you consider them numerical values.[color=blue]
> However, they are sequential if you consider them time-based entities.[/color]

The code snippet in your earlier post is concatenating the millisecond
component before the remainder of time. Consequently, sequential time
values won't be stored consecutively in the ordered index. This is
consideration because it introduces index fragmentation and lessens the
effectiveness of index.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Des L. Davis" <des.davis@cogeco.ca> wrote in message
news:D3GGc.37152$_V4.33574@read1.cgocable.net...[color=blue]
> I'm in the process of re-writing the key generating algorithm to eliminate
> the WAITFOR DELAY. Thanks for all the valuable input.
>
> Dan,
>
> The keys appear non-sequential if you consider them numerical values.
> However, they are sequential if you consider them time-based entities.
>
>
> "Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
> news:aMxGc.6914$oD3.707@newsread1.news.pas.earthli nk.net...[color=green][color=darkred]
> > > The obvious solution here is to re-work how the keys are generated but[/color][/color]
> I'm[color=green][color=darkred]
> > > attempting to better understand what are my real options.[/color]
> >
> > IMHO, you'll be better off generating a surrogate values using an[/color]
> different[color=green]
> > algorithm. The IDENTITY approach Erland suggested will perform well,
> > guarantee uniqueness and handle concurrency. I'm interested in knowing[/color]
> why[color=green]
> > you are deliberately assigning non-sequential values. More often than[/color]
> not,[color=green]
> > 'hot spots' are a good thing in SQL 7 and above due to row-level[/color][/color]
locking.[color=blue][color=green]
> >
> > From your description, it appears there is a cursor loop involved[/color]
> somewhere,[color=green]
> > although it's unclear to me whether this is in sp_SaveImportedData,[/color]
> another[color=green]
> > proc or ImportData.EXE. In any case, if you want to keep your existing
> > algorithm, consider replacing the millisecond component with your own
> > sequence number that is initialized whenever the time component changes.
> > The untested Transact-SQL script below illustrates this technique.
> >
> > DECLARE @DateComponent char(7)
> > DECLARE @TimeComponent char(6)
> > DECLARE @LastDateComponent char(7)
> > DECLARE @LastTimeComponent char(6)
> > DECLARE @PK char(16)
> > DECLARE @Sequence int
> >
> > WHILE --some loop condition
> > BEGIN
> >
> > SET @DateComponent =
> > dbo. PadLeft ( @Year, '0', 4 ) +
> > dbo.PadLeft ( @DayOfYear, '0', 3 )
> > SET @TimeComponent =
> > dbo.PadLeft ( @Hour, '0', 2 ) +
> > dbo.PadLeft ( @Minute, '0', 2 ) +
> > dbo.PadLeft ( @Second, '0', 2 )
> >
> > IF @DateComponent + @TimeComponent <> @LastDateComponent +
> > @LastTimeComponent
> > BEGIN
> > SET @Sequence = 0
> > SET @LastDateComponent = @DateComponent
> > SET @LastTimeComponent = @TimeComponent
> > END
> > ELSE
> > BEGIN
> > SET @Sequence = @Sequence + 1
> > END
> > SET @PK =
> > @DateComponent +
> > dbo.PadLeft ( @Sequence, '0', 3 ) +
> > @TimeComponent
> >
> > --other code here
> > END
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> >[/color]
>
>[/color]

re: WAITFOR DELAY

Dan,

You've been awesome and I'm very appreciative. Thank you


"Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
news:b2IGc.6989$R36.4269@newsread2.news.pas.earthl ink.net...[color=blue][color=green]
> > The keys appear non-sequential if you consider them numerical values.
> > However, they are sequential if you consider them time-based entities.[/color]
>
> The code snippet in your earlier post is concatenating the millisecond
> component before the remainder of time. Consequently, sequential time
> values won't be stored consecutively in the ordered index. This is
> consideration because it introduces index fragmentation and lessens the
> effectiveness of index.
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Des L. Davis" <des.davis@cogeco.ca> wrote in message
> news:D3GGc.37152$_V4.33574@read1.cgocable.net...[color=green]
> > I'm in the process of re-writing the key generating algorithm to[/color][/color]
eliminate[color=blue][color=green]
> > the WAITFOR DELAY. Thanks for all the valuable input.
> >
> > Dan,
> >
> > The keys appear non-sequential if you consider them numerical values.
> > However, they are sequential if you consider them time-based entities.
> >
> >
> > "Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
> > news:aMxGc.6914$oD3.707@newsread1.news.pas.earthli nk.net...[color=darkred]
> > > > The obvious solution here is to re-work how the keys are generated[/color][/color][/color]
but[color=blue][color=green]
> > I'm[color=darkred]
> > > > attempting to better understand what are my real options.
> > >
> > > IMHO, you'll be better off generating a surrogate values using an[/color]
> > different[color=darkred]
> > > algorithm. The IDENTITY approach Erland suggested will perform well,
> > > guarantee uniqueness and handle concurrency. I'm interested in[/color][/color][/color]
knowing[color=blue][color=green]
> > why[color=darkred]
> > > you are deliberately assigning non-sequential values. More often than[/color]
> > not,[color=darkred]
> > > 'hot spots' are a good thing in SQL 7 and above due to row-level[/color][/color]
> locking.[color=green][color=darkred]
> > >
> > > From your description, it appears there is a cursor loop involved[/color]
> > somewhere,[color=darkred]
> > > although it's unclear to me whether this is in sp_SaveImportedData,[/color]
> > another[color=darkred]
> > > proc or ImportData.EXE. In any case, if you want to keep your[/color][/color][/color]
existing[color=blue][color=green][color=darkred]
> > > algorithm, consider replacing the millisecond component with your own
> > > sequence number that is initialized whenever the time component[/color][/color][/color]
changes.[color=blue][color=green][color=darkred]
> > > The untested Transact-SQL script below illustrates this technique.
> > >
> > > DECLARE @DateComponent char(7)
> > > DECLARE @TimeComponent char(6)
> > > DECLARE @LastDateComponent char(7)
> > > DECLARE @LastTimeComponent char(6)
> > > DECLARE @PK char(16)
> > > DECLARE @Sequence int
> > >
> > > WHILE --some loop condition
> > > BEGIN
> > >
> > > SET @DateComponent =
> > > dbo. PadLeft ( @Year, '0', 4 ) +
> > > dbo.PadLeft ( @DayOfYear, '0', 3 )
> > > SET @TimeComponent =
> > > dbo.PadLeft ( @Hour, '0', 2 ) +
> > > dbo.PadLeft ( @Minute, '0', 2 ) +
> > > dbo.PadLeft ( @Second, '0', 2 )
> > >
> > > IF @DateComponent + @TimeComponent <> @LastDateComponent +
> > > @LastTimeComponent
> > > BEGIN
> > > SET @Sequence = 0
> > > SET @LastDateComponent = @DateComponent
> > > SET @LastTimeComponent = @TimeComponent
> > > END
> > > ELSE
> > > BEGIN
> > > SET @Sequence = @Sequence + 1
> > > END
> > > SET @PK =
> > > @DateComponent +
> > > dbo.PadLeft ( @Sequence, '0', 3 ) +
> > > @TimeComponent
> > >
> > > --other code here
> > > END
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

re: WAITFOR DELAY

Glad it helped.

--
Dan Guzman
SQL Server MVP