getdate() accuracy

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » getdate() accuracy

34 posts, Page 1 of 4

1 2 3 4 »»»

getdate() accuracy

Rate Topic

Display Mode

Topic Options

Author

Message

JacekO

Posted Wednesday, May 14, 2008 8:05 PM

SSC-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

Jeff Moden

Posted Wednesday, May 14, 2008 10:41 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:40 AM
Points: 32,893, Visits: 26,774

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #501004

Jeff Moden

Posted Wednesday, May 14, 2008 10:48 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:40 AM
Points: 32,893, Visits: 26,774

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

Post #501008

JacekO

Posted Thursday, May 15, 2008 7:43 AM

SSC-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

Scalability Doug

Posted Thursday, May 15, 2008 8:00 AM

SSChasing Mays

Group: General Forum Members
Last Login: Thursday, April 05, 2012 12:43 PM
Points: 655, Visits: 6,097

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

Jeff Moden

Posted Thursday, May 15, 2008 8:07 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:40 AM
Points: 32,893, Visits: 26,774

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

Post #501342

Scalability Doug

Posted Thursday, May 15, 2008 8:11 AM

SSChasing Mays

Group: General Forum Members
Last Login: Thursday, April 05, 2012 12:43 PM
Points: 655, Visits: 6,097

The GETDATE() function returns a DATETIME type. Therefore it will inherit the same rounding limitation.

DAB

Post #501347

Steve Jones - SSC Editor

Posted Thursday, May 15, 2008 9:01 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 9:31 AM
Points: 31,409, Visits: 13,724

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

Matt Miller (#4)

Posted Thursday, May 15, 2008 9:21 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, May 17, 2013 1:56 PM
Points: 6,997, Visits: 13,941

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

Jeff Moden

Posted Thursday, May 15, 2008 5:36 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:40 AM
Points: 32,893, Visits: 26,774

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

Post #501716

« Prev Topic | Next Topic »

34 posts, Page 1 of 4

1 2 3 4 »»»

Permissions