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 Wednesday, May 14, 2008 8:05 PM
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
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?


---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.
Post #500971
Posted Wednesday, May 14, 2008 10:41 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 1:47 PM
Points: 35,215, Visits: 31,667
The CPU does other things... it's not dedicated to running your code.

--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 #501004
Posted Wednesday, May 14, 2008 10:48 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 1:47 PM
Points: 35,215, Visits: 31,667
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
"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 #501008
Posted Thursday, May 15, 2008 7:43 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
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


---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.
Post #501307
Posted Thursday, May 15, 2008 8:00 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, April 2, 2014 2:01 PM
Points: 659, Visits: 6,104
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
Post #501329
Posted Thursday, May 15, 2008 8:07 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 1:47 PM
Points: 35,215, Visits: 31,667
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
"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 #501342
Posted Thursday, May 15, 2008 8:11 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, April 2, 2014 2:01 PM
Points: 659, Visits: 6,104
The GETDATE() function returns a DATETIME type. Therefore it will inherit the same rounding limitation.


DAB
Post #501347
Posted Thursday, May 15, 2008 9:01 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 4:16 PM
Points: 31,018, Visits: 15,456
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.










Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #501400
Posted Thursday, May 15, 2008 9:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:57 PM
Points: 7,064, Visits: 15,270
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?
Post #501426
Posted Thursday, May 15, 2008 5:36 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 1:47 PM
Points: 35,215, Visits: 31,667
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
"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 #501716
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse