May 14, 2008 at 8:05 pm
DECLARE @I INT
SET @I = 1
WHILE @I < 10000
BEGIN
SELECT getdate()
SET @I = @I + 1
END
Run the above in query analyzer.
I was expecting to get a ton of records that will increment the time by 3 or 4 ms,but what I see is a set of records that increment on average about 14 ms. (not every record gets the incremented time of course, there are tens of records with one time and then another set of records with the incremented time)
Funny part is if I output to a file instead the increment jumps to about 30 ms.
What is wrong in my assumption about the 3 or 4 ms?
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
May 14, 2008 at 10:41 pm
The CPU does other things... it's not dedicated to running your code.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 14, 2008 at 10:48 pm
The following shows the accuracy you're talking about...
SELECT 1,CAST('2000-01-01 23:59:59.990' AS DATETIME) UNION ALL
SELECT 2,CAST('2000-01-01 23:59:59.991' AS DATETIME) UNION ALL
SELECT 3,CAST('2000-01-01 23:59:59.992' AS DATETIME) UNION ALL
SELECT 4,CAST('2000-01-01 23:59:59.993' AS DATETIME) UNION ALL
SELECT 5,CAST('2000-01-01 23:59:59.994' AS DATETIME) UNION ALL
SELECT 6,CAST('2000-01-01 23:59:59.995' AS DATETIME) UNION ALL
SELECT 7,CAST('2000-01-01 23:59:59.996' AS DATETIME) UNION ALL
SELECT 8,CAST('2000-01-01 23:59:59.997' AS DATETIME) UNION ALL
SELECT 9,CAST('2000-01-01 23:59:59.998' AS DATETIME) UNION ALL
SELECT 10,CAST('2000-01-01 23:59:59.999' AS DATETIME)
Results:
----------- ------------------------------------------------------
1 2000-01-01 23:59:59.990
2 2000-01-01 23:59:59.990
3 2000-01-01 23:59:59.993
4 2000-01-01 23:59:59.993
5 2000-01-01 23:59:59.993
6 2000-01-01 23:59:59.997
7 2000-01-01 23:59:59.997
8 2000-01-01 23:59:59.997
9 2000-01-01 23:59:59.997
10 2000-01-02 00:00:00.000
(10 row(s) affected)
--Jeff Moden
Change is inevitable... Change for the better is not.
May 15, 2008 at 7:43 am
I need this for logging in a multiprocess, multithreaded environment.
I understand that the CPU does other things but even when I have multiple callers call the logging SP (I have one field in the DB with a default getdate() ) I can not break the 14 or so ms barrier.
The second example does not show the getdate() granuality but the DATETIME granuality. I am wondering if the gatdate() inherently can not handle the full spectrum of DATETIME values.
Do you have access to a fast PC that you could run this test and see if on a faster machine you get better then the 14 ms?
The one I am using right now is a single core 2.13GHz
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
May 15, 2008 at 8:00 am
Look at BOL: "datetime values are rounded to increments of .000, .003, or .007 seconds, as shown in the following table."
If you need that extra precision, look into upgrading to SQL 2008 and using the DATETIME2 and TIME data types.
DAB
May 15, 2008 at 8:07 am
That's good info... but like JackO said, that's for DATETIME precision... not GETDATE precision. We're trying to figure out why a high speed loop seems to indicate that GETDATE won't return that precision.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 15, 2008 at 8:11 am
The GETDATE() function returns a DATETIME type. Therefore it will inherit the same rounding limitation.
DAB
May 15, 2008 at 9:01 am
It is the same precision, but can you get
7:00:00.000
7:00:00.000
7:00:00.003
7:00:00.003
7:00:00.007
7:00:00.007
On my desktop (dual core, 2GHz), I get:
.200 (repeated over 100 times)
.310 (repeated, difference of 110ms)
.373 (repeated, difference of 63ms)
.467 (repeated, difference of 94ms)
On our SQL 2K server, 2x2.39GHz
.653
.843
.030 (next second)
.187
I think this is something to do with the client and batching rather than precision. If I do this server side, I get:
.247
.263
.280
.293
.310
.327
.340
multiples of each, alternating between 13 and 17ms. About in counts of 20-25 for each value. Code:
create table logger( mydate datetime)
go
DECLARE @I INT
SET @I = 1
WHILE @I < 10000
BEGIN
insert logger select getdate()
SET @I = @I + 1
END
go
select * from logger
My guess is that in a tight loop like this one, there's still delays as the CPU switches over, the IO catches up (think two sets of writing (log + data) and maybe other efficiencies.
SQL Server isn't designed as a real time data capture. It will log to the thousandth of the second (within the 0, 3, 7 values), but it can't necessarily log every ms. You need a real-time capture device to get this and then insert into SQL Server. Even if you send stuff that fast, it won't necessarily insert and be committed in that speed.
May 15, 2008 at 9:21 am
I hate to say this - but doing a straight select like the initial loop you have, and....I get 10,000 selects, all with the SAME date value.... The only way I get any kind of variation is to do 10,000 1-row inserts, and at that point, there are bunches of duplicate times and the jumps in time are a little all over the place (from 13ms to 33ms).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 15, 2008 at 5:36 pm
SQLServerLifer (5/15/2008)
The GETDATE() function returns a DATETIME type. Therefore it will inherit the same rounding limitation.DAB
Cool! Show us the code that proves it because non of the rest of us can get anything less that about 13 ms between skips.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 15, 2008 at 6:59 pm
First, to observe the rounding that occurs when working with DATETIME values, run the following:
SET NOCOUNT ON
DECLARE @Tb TABLE (Dt DATETIME)
DECLARE @dt DATETIME, @Cnt INT, @I INT
SET @Cnt = 1 --Change this value and observe...
SET @dt = '2008-05-15 17:11:06.390'
SET @I = 0
WHILE @I < 10 --Change this value and observe as well...
BEGIN
INSERT @Tb
SELECT @dt
SET @I = @I + 1
SET @dt = DATEADD(ms, @Cnt, @dt)
END
SELECT (@Cnt * @I) as Dif_Expected, DATEDIFF(ms, MIN(Dt), MAX(Dt)) as Dif_Actual FROM @Tb
Notice that the DATETIME value never changes if we are just adding 1ms at a time. Think about this over 10,000 iterations and the difference between actual and expected becomes significant.
Looking at GETDATE(), it is very clearly returns a DATETIME value and therefore will be rounded. Consider the results of this:
SELECT CAST('2008-05-15 17:11:06.390' AS DATETIME) UNION ALL
SELECT CAST('2008-05-15 17:11:06.391' AS DATETIME) UNION ALL
SELECT CAST('2008-05-15 17:11:06.392' AS DATETIME) UNION ALL
SELECT CAST('2008-05-15 17:11:06.393' AS DATETIME) UNION ALL
SELECT CAST('2008-05-15 17:11:06.394' AS DATETIME) UNION ALL
SELECT CAST('2008-05-15 17:11:06.402' AS DATETIME)
Making sense?
Now, looking at the GETDATE() as a part of the inserts, let's consider the loop above. Using it, if we are to do 10,000 iterations with an addition of 1ms each, we would expect 10,000ms or 10sec. Would you be happy if it took SQL 10sec to insert 10,000 dates? Having tried the INSERT loops in previous posts on several SQL servers, the worst time I saw was about 400ms total execution. MUCH better than 10sec. Now consider if there was a 3ms gap on each one, waiting for 30sec! To see my point, run this:
SET NOCOUNT ON
SELECT GETDATE()
DECLARE @Tb TABLE (Dt DATETIME)
DECLARE @I INT
SET @I = 1
WHILE @I < 10000
BEGIN
INSERT @Tb
SELECT GETDATE()
SET @I = @I + 1
END
SELECT GETDATE()
SELECT Dt, COUNT(Dt) FROM @Tb GROUP BY Dt
I am sure you will see that while there are "gaps" in the GETDATE() values, they are not there from processing a single record, but from processing several hundred.
Hopefully all this is helping me get to my point, which is that we can't expect to see a perfect interval on DATETIME values while inserting. As has been already said, other things are happening on the server. SQL is coordinating events behind the scene maintaining performance and integrity for us. Like Steve said above, SQL isn't for real time capture. For that matter, Windows isn't a real time OS.
Hopefully all this makes sense and it isn't too badly formed as I am now running out the door! I'll look to edit any major mistakes later.:)
May 16, 2008 at 7:20 am
OK. I did more testing and more investigating and would like to clarify to all what is going on.
First of all the 15 or so ms gaps are not a result of the CPU attending to other tasks. The task switching is much faster then this interval. I run some tests and with multiple threads storing the data the records are intermixed between sources but the time gap remains.
The issue is the Windows system timer. It is being updated 64 times per second and this generates the 15.6 (or so) ms intervals. I read somewhere that sometimes this can be changed by some processes but did not find the time to dive into this yet. Some other sources claim that on some systems the timer is updated every 10 ms.
So my instinct that this is the getdate() function's internal workings that generate the gaps was correct. Since it uses the system clock and the system clock is quite primitive we can not expect to get the 3 or so accuracy the DATETIME data type supports because of the limitation of the system timer.
I run my tests on my WinXP box. I did not have a chance to run it on a Win 2003 server so I don't know if it works the same way or not.
So Steve , if you want another question of the day you may explore this issue. 🙂
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
May 16, 2008 at 7:28 am
Very cool, JackO... that's very good to know. Thanks for posting your findings.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 16, 2008 at 7:36 am
There is another twist to this story. The value that the system timer presents to the callers is sometimes interpreted differently by the callers. I run some tests where I read the system time in a .NET app, called a SP to store it in the DB where one of the fields had a default of getdate().
The explicitly entered time value received from the .NET was almost always different by 3 ms then the time generated by the getdate(). The funny part is that the .NET time value was 3ms later then the getdate() value. It looked like the SQL read the time before I decided to insert the record.
PS.
Jeff,
It is Jacek not Jack. 😉
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
May 16, 2008 at 8:04 am
JacekO (5/16/2008)
PS.Jeff,
It is Jacek not Jack. 😉
Dang... I'm so sorry... I'm usually the one who get's ticked when someone mispells "Jeff" (believe it or not, it happens). Old eyes playing tricks on me. Thank you for the correction.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply