WAITFOR DELAY too slow? - 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

General SQL Server Forums

Script Library

WAITFOR DELAY too slow?

Author

Topic

Doug_Castell
Starting Member

3 Posts

Posted - 2006-06-23 : 13:43:34

http://www.castellcomputers.com/?p=44

This bit of SQL script can be used to insert a pause of one hundredth of a second in a trigger, stored procedure or SQL script. This can be useful for when MSSQL’s built-in WAITFOR DELAY function is just too long (it’s minimum increment is a full second!)


declare @later datetime
declare @now datetime
set @later = current_timestamp+'00:00:00.01'
set @now = current_timestamp
while @now < @later
begin
set @now = current_timestamp
end

I'm sure it could be further tweaked to become a function.. And heck, maybe one of the gurus will tell me that there's something i don't know about WAITFOR or whatnot.. Feedback appreciated!

--
Doug Castell
GoldMine Guru
www.castellcomputers.com

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-23 : 14:40:24

WAITFOR DELAY works fine for less than 1 second.

See code and results below.


eclare @start datetime

set @start = getdate()
waitfor delay '00:00:00.500'
print	'Elapsed Time = '+
	right(convert(varchar(30),getdate()-@start,121),12)

set @start = getdate()
waitfor delay '00:00:00.200'
print	'Elapsed Time = '+
	right(convert(varchar(30),getdate()-@start,121),12)


set @start = getdate()
waitfor delay '00:00:00.010'
print	'Elapsed Time = '+
	right(convert(varchar(30),getdate()-@start,121),12)

Results:


Elapsed Time = 00:00:00.517
Elapsed Time = 00:00:00.203
Elapsed Time = 00:00:00.017

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources