Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

getdate() accuracy Expand / Collapse
Author
Message
Posted Thursday, May 15, 2008 6:59 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 12, 2014 1:31 PM
Points: 99, Visits: 639
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.:)



Post #501728
Posted Friday, May 16, 2008 7:20 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 29, 2011 2:59 PM
Points: 473, Visits: 606
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. :)


---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.
Post #502028
Posted Friday, May 16, 2008 7:28 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:42 PM
Points: 35,216, Visits: 31,672
Very cool, JackO... that's very good to know. Thanks for posting your findings.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #502036
Posted Friday, May 16, 2008 7:36 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 29, 2011 2:59 PM
Points: 473, Visits: 606
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. ;)


---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.
Post #502043
Posted Friday, May 16, 2008 8:04 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:42 PM
Points: 35,216, Visits: 31,672
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #502082
Posted Friday, May 16, 2008 3:07 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,294, Visits: 9,480
SQLServerLifer (5/15/2008)
The GETDATE() function returns a DATETIME type. Therefore it will inherit the same rounding limitation.


Yes, but it might add "Limitations" of its own. In particular (and what this discussion is really about), the internal speed or increment of the "clock" that SQL Server is using may be larger than 3.33ms. If fact, it may be 13 or 14ms.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #502371
Posted Friday, May 16, 2008 3:21 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,294, Visits: 9,480
Oops, apparently, I was a little late to this party (even Matt got here before me!), but I believe that it is now apparent what I was saying above: this is an issues with the SQL Server* Clock resolution, and NOT the resolution of the datatype.

*(note that an application or sw server's clock is not necessairly the same as the system's clock, but it's resolution is normaly always a multiple (1 or more) of the system clock's resolution, never less (without special hardware)).


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #502377
Posted Friday, May 16, 2008 3:31 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:27 AM
Points: 7,064, Visits: 15,274
rbarryyoung (5/16/2008)
...(even Matt got her before me!)...


Hey now! where the heck did THAT come from???

Sheesh - travel home to see the wife, and get smacked around on the boards...:)


----------------------------------------------------------------------------------
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?
Post #502381
Posted Friday, May 16, 2008 3:55 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 12, 2014 1:31 PM
Points: 99, Visits: 639
Well, that's better than the other way around, Matt! :D


Post #502392
Posted Friday, May 16, 2008 4:46 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,294, Visits: 9,480
Matt Miller (5/16/2008)
rbarryyoung (5/16/2008)
...(even Matt got here before me!)...


Hey now! where the heck did THAT come from???

Sheesh - travel home to see the wife, and get smacked around on the boards...:)

From you & Jack, mostly :D ...

here: http://www.sqlservercentral.com/Forums/FindPost468098.aspx

and here: http://www.sqlservercentral.com/Forums/FindPost468108.aspx

Hey, I don't make this stuff up! I make other stuff up, but not this stuff! :)


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #502404
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse