February 3, 2012 at 10:13 am
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
February 3, 2012 at 1:36 pm
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:
Duration Iterations StartTime EndTime
977 51 2012-02-03 14:37:30.7926123 2012-02-03 14:37:30.7935890
976 200 2012-02-03 14:37:30.7935890 2012-02-03 14:37:30.7945657
977 267 2012-02-03 14:37:30.7945657 2012-02-03 14:37:30.7955424
977 265 2012-02-03 14:37:30.7955424 2012-02-03 14:37:30.7965191
. . .
Duration (No column name)
976 2960
977 6911
1953 13
1954 12
2930 32
2931 2
3906 1
3907 15
... and 48 others with a longer duration up to 620204
The faster machine gave this:
Duration Iterations StartTime EndTime
1000 63 2012-02-03 14:38:58.9925551 2012-02-03 14:38:58.9935551
1000 347 2012-02-03 14:38:58.9935551 2012-02-03 14:38:58.9945551
1000 423 2012-02-03 14:38:58.9945551 2012-02-03 14:38:58.9955551
1000 449 2012-02-03 14:38:58.9955551 2012-02-03 14:38:58.9965551
. . .
All of the times ended in 5551.
Duration (No column name)
1000 9713
3000 152
4000 131
7000 1
300000 2
304000 1
February 3, 2012 at 3:03 pm
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:
February 4, 2012 at 7:14 am
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'.
Viewing 4 posts - 61 through 63 (of 63 total)
You must be logged in to reply to this topic. Login to reply