Do you know your getdate()?

  • I, too, ran this code on one server and was getting 3 milliseconds. I was curious as to the duration of the test and the inner workings of the code so I wrote this to remove the code from the loop and check on a few things.

    I found that the results are highly dependent on the hardware configurations.

    DECLARE @TimeStart DATETIME

    DECLARE @Time DATETIME

    DECLARE @TimeEnd DATETIME

    Declare @TotalTime int

    DECLARE @Count INT

    Declare @TotalCount int

    Declare @HighCount int

    Declare @LowCount int

    Declare @HighTime int

    Declare @LowTime int

    Declare @IterTime int

    Declare @OddTime int

    DECLARE @I INT

    Declare @TestStart datetime

    Declare @TestEnd dateTime

    SET @I = 0

    Set @TotalTime = 0

    Set @TotalCount = 0

    Set @HighCount = 0

    Set @LowCount = 99999999

    Set @HighTime = 0

    Set @LowTime = 99999999

    Set @IterTime = 0

    Set @OddTime = 0

    Set @TestStart = GETDATE()

    WHILE @I < 10000

    BEGIN

    SET @TimeStart = GETDATE()

    SET @TimeEnd = @TimeStart

    Set @Count = 0

    -- Find the amount of time it takes to get a different value

    -- To get strictly time, comment out the Set @Count line

    WHILE @TimeStart = @TimeEnd

    BEGIN

    SET @TimeEnd = GETDATE()

    Set @Count = @Count + 1

    End

    Set @IterTime = DATEDIFF(MILLISECOND, @TimeStart, @TimeEnd)

    Set @TotalTime = @TotalTime + @IterTime

    Set @TotalCount = @TotalCount + @Count

    Set @I = @I + 1

    If @Count < @LowCount

    Set @LowCount = @Count

    If @Count > @HighCount

    Set @HighCount = @Count

    If @IterTime < @LowTime

    Set @LowTime = @IterTime

    If @IterTime > @HighTime

    Set @HighTime = @IterTime

    -- Why 3? Because I ran the inner loop 100 times while building this and it was nearly always returning 3

    --

    If @IterTime <> 3

    Set @OddTime = @OddTime + 1

    End

    Set @TestEnd = GETDATE()

    Print 'Test Duration'

    print DATEDIFF(MILLISECOND, @teststart, @testend) / 1000

    print '---------------------------'

    print 'Average Iterations'

    print @TotalCount / @I

    print 'High Iterations'

    print @HighCount

    print 'Low Iterations'

    print @LowCount

    print '-----------------------'

    print 'Average Time'

    print @TotalTime / @I

    print 'High Time'

    print @HighTime

    print 'Low Time'

    print @LowTime

    print 'Odd Time Count'

    print @OddTime

    When I ran this on a machine with NT6.1 x64, 40 gigs of memory, and 16 processors I got these results:

    Test Duration

    37

    ---------------------------

    Average Iteratations

    1017

    High Iterations

    2301

    Low Iterations

    2

    -----------------------

    Average Time

    3

    High Time

    306

    Low Time

    3

    Odd Time Count

    115

    So 115 times out of 10,000 the result wasn't 3 milliseconds.

    On a machine (a sandbox) NT 6.0 x86 with 4 gigs of memory and 1 processor I got these results:

    Test Duration

    182

    ---------------------------

    Average Iteratations

    4339

    High Iterations

    6284

    Low Iterations

    25

    -----------------------

    Average Time

    17

    High Time

    483

    Low Time

    3

    Odd Time Count

    9999

  • Well I tried running sysdatetime on these 2 servers and wondered about the results.

    So I modified my query to dump the results into a table so I could see better. The results surprised me.

    DECLARE @TimeStart DATETIME2

    DECLARE @TimeEnd DATETIME2

    DECLARE @Count INT

    Declare @Duration int

    DECLARE @I INT

    Declare @results table(Duration int, Iterations int, StartTime datetime2, EndTime datetime2)

    SET @I = 0

    Set @Duration = 0

    WHILE @I < 10000

    BEGIN

    SET @TimeStart = sysdatetime()

    SET @TimeEnd = @TimeStart

    Set @Count = 0

    -- Find the amount of time it takes to get a different value

    -- To get strictly time, comment out the Set @Count line

    WHILE @TimeStart = @TimeEnd

    BEGIN

    SET @TimeEnd = sysdatetime()

    Set @Count = @Count + 1

    End

    Set @Duration = DATEDIFF(MICROSECOND, @TimeStart, @TimeEnd)

    Set @I = @I + 1

    Insert into @results(Duration, Iterations, StartTime, EndTime)

    values (@Duration, @Count, @TimeStart, @TimeEnd)

    End

    select Duration

    , Iterations

    , StartTime

    , EndTime

    from @results

    Select AVG(duration)

    From @results

    select Duration

    , count(Duration)

    from @results

    group by Duration

    The slower machine gave this:

    DurationIterationsStartTimeEndTime

    977512012-02-03 14:37:30.79261232012-02-03 14:37:30.7935890

    9762002012-02-03 14:37:30.79358902012-02-03 14:37:30.7945657

    9772672012-02-03 14:37:30.79456572012-02-03 14:37:30.7955424

    9772652012-02-03 14:37:30.79554242012-02-03 14:37:30.7965191

    . . .

    Duration(No column name)

    9762960

    9776911

    195313

    195412

    293032

    29312

    39061

    390715

    ... and 48 others with a longer duration up to 620204

    The faster machine gave this:

    DurationIterationsStartTimeEndTime

    1000632012-02-03 14:38:58.99255512012-02-03 14:38:58.9935551

    10003472012-02-03 14:38:58.99355512012-02-03 14:38:58.9945551

    10004232012-02-03 14:38:58.99455512012-02-03 14:38:58.9955551

    10004492012-02-03 14:38:58.99555512012-02-03 14:38:58.9965551

    . . .

    All of the times ended in 5551.

    Duration(No column name)

    10009713

    3000152

    4000131

    70001

    3000002

    3040001

  • Don't think I've seen this configuration yet:

    SQL 2008 SP2 on an Itanium 64

    Windows 2003 SP2

    1st test-

    132273 (wow)

    8.02182

    A test machine with nothing going on but a completely full data drive (shouldn't matter)

    No 2nd test 'casue the 1st took almost 18 minutes to run :w00t:

  • My contribution to the body of data:

    Dell Inspiron 9400 laptop

    Intel Core2 T5600 CPU

    4GB RAM

    Windows 7 Enterprise SP1 64-bit

    Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)

    Jun 17 2011 00:54:03

    Copyright (c) Microsoft Corporation

    Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

    Query results:

    5138

    3.33982

    CURRENT_TIMESTAMP seems to work in 2008 R2:

    SELECT CURRENT_TIMESTAMP

    -----------------------

    2012-02-04 09:06:08.757

    SELECT CURRENT_DATE

    Incorrect syntax near the keyword 'CURRENT_DATE'.


    Peter MaloofServing Data

Viewing 4 posts - 61 through 64 (of 64 total)

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