Do you know your getdate()?

  • JacekO

    SSCertifiable

    Points: 6347

    Comments posted to this topic are about the item Do you know your getdate()?

    ---------------------------------------------
    [font="Verdana"]Nothing is impossible.
    It is just a matter of time and money.[/font]

  • ziangij

    SSCertifiable

    Points: 6571

    thanks :-)... was really not aware of this stuff... also a good interview question...

  • brian.neumeier-603907

    Old Hand

    Points: 346

    Is there a reason you chose not to perform the same tests on the GETUTCDATE function?

  • bhuvii

    Grasshopper

    Points: 15

    Testing the precision of GETDATE()...

    instead of getting values 595 and 15.6521,

    i got

    4262 -out of 10 million calls i got distinct 4262 values returned by GETDATE()

    and

    3.33318 - what is the precision of the GETDATE() function

    which is supposed to be true.

    i ran the given query in the post, which the answer is supposed to be true.

    i ran the query against SQL SERVER 2008

    if i am wrong somewhere, let me know

  • Kari Suresh

    Hall of Fame

    Points: 3712

    Buddies,

    I am using SS2005 and I ran the first query: DECLARE @TimeStart DATETIME

    DECLARE @Time DATETIME

    DECLARE @TimeEnd DATETIME

    DECLARE @I INT

    DECLARE @Count INT

    SET @I = 0

    SET @Count = 0

    SET @TimeStart = GETDATE()

    SET @Time = @TimeStart

    WHILE @I < 10000000

    BEGIN

    SET @TimeEnd = GETDATE()

    IF @TimeEnd <> @Time

    BEGIN

    SET @Count = @Count + 1

    SET @Time = @TimeEnd

    END

    SET @I = @I + 1

    END

    PRINT @Count

    PRINT DATEDIFF(millisecond, @TimeStart, @TimeEnd) / CAST(@Count AS REAL)

    I have got the values: 6519 and 3.33379.

    I ran the same query again to re-verify, but I have got: 6518 and 3.3363.

    The precision is 3.33 for GETDATE() function.

    How these values are vary (595 and 15.6521 - 6519 and 3.33379) ?

    KSB
    -----------------------------
    Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha

  • uhlmann

    Grasshopper

    Points: 15

    Same result ...

    15020 and 3.3462

    SQL 2005 Developer on Vista 64bit

    Edit:

    Same test on SQL 2008 Developer on the same machine

    2900 and 15.5941

  • peter.row

    SSCarpal Tunnel

    Points: 4295

    I ran the same 2 tests on Win 7 64bit Quad core, 8GB Ram machine running SQL Server 2008 Developer 64bit.

    And got the following:

    GetDate(): 2604 values with precision 3.33333 milliseconds

    SysDateTime(): 13930 values with precision 1000 microseconds, (1 millisecond)

    Judging by the rest of the comments so far it seems to be that XP can't hack it whilst latest OS' can (unless CPU cores played a part).

    However in production you would never use XP, Vista or 7 to run your SQL Server so more interesting would be results on other Windows server versions.

  • Daniel Tonagel

    SSC Rookie

    Points: 39

    Some more SYSDATETIME Results:

    SQL 2008 R2 on Windows 7 x64: 1 ms

    SQL 2008 R2 on Win 2008 R2 on HyperV on Win 2008 R2: 15.5 ms

    SQL 2008 R2 on Win 2008 R2 on VMWare on Win 7 x64: 1.3 ms

  • hakan.borneland

    SSC Enthusiast

    Points: 198

    ... and another test

    Win XP 32 bit SP3

    SQL Server 2005 SP3 (9.0.4262)

    CPU 2 processors (Core duo)

    4517 and 3.32809

  • matthieu.ravard

    Grasshopper

    Points: 15

    is the other instructions present in the code takes some millisecond ?

  • SQLZ

    SSChampion

    Points: 12872

    I get 3.3 ms here using your own code.

    The worst part about it is you would have not given the job to someone who correctly responded with 3.3ms for GETDATE() precision:-P

  • adrian.buzila

    Ten Centuries

    Points: 1116

    If you get 15.6521 it does not mean that this is the precision of getdate()!

    Why do you think that @Time and @TimeEnd have succesiv values?

    The conclusion, in my opinion, is the your system is very good (count is only 595 so only 595 times @Time and @TimeEnd are different, on my system is gets 18475), but the load of you system is very high, your processor is taken by another process from time to time so you get a very big value for the "precision" (in my case is 3.34344).

  • Daniel Tonagel

    SSC Rookie

    Points: 39

    matthieu.ravard (5/12/2010)


    is the other instructions present in the code takes some millisecond ?

    Doesn't matter as long as you get several loop iterations before the timer gives another value.

  • wschampheleer

    SSCertifiable

    Points: 5504

    WIN XP SP3 / SQL Server 2008 SP1 on dual core machine

    getdate: 6043 3.3399 & 5957 3.33557

    sysdatetime: 1713 15625 & 1789 15625

    Regards,

    Willem
    http://wschampheleer.wordpress.com[/url]

  • Leendert van Staalduinen

    Right there with Babe

    Points: 767

    My results on a single Windows 2000 Professional SP4 machine:

    - In SQL Server 2000 Query Analyzer: did not run within 5 minutes runtime, I do not understand why.

    - In SQL Server 2000 SP4, via Access .ADP file: 1,418 / 15.62,

    - In SQL Server 2005 SP2, via Access .ADP file: 789 / 15.62.

    Hope, this adds to the discussion, Leendert.

Viewing 15 posts - 1 through 15 (of 64 total)

You must be logged in to reply to this topic. Login to reply