5

Is there any reason why I should stop using SYSDATETIME() everytime, instead of GETDATE() ?

Don't they both ask the cpu what time it is or does sysdatetime need more instructions to calculate the fractions? Does Getdate work on rounding it? Can sysdatetime be faster because it's not working on rounding?

I obviously wouldn't use sysdatetime if I'm not storing the nanoseconds, but I'm asking about the costs other than the storage size. (The current app I'm developing runs sysdatetime() at least 280 times a second)

Uğur Gümüşhan
  • 2,455
  • 4
  • 34
  • 62
  • 1
    As per the [FAQ](http://stackoverflow.com/faq), questions should be `based on actual problems that you face`. Do you have a specific reason for thinking you should *not* use `SYSDATETIME`? If not, this question is probably too open-ended to be suitable for answering here. – RB. Feb 13 '13 at 15:07
  • 1
    My only thought would be that `SYSDATETIME()` may have a higher cost because of it's precision. – Kermit Feb 13 '13 at 15:08
  • @RB The current app I'm developing runs sysdatetime() at least 280 times a second. – Uğur Gümüşhan Feb 13 '13 at 15:09
  • @UğurGümüşhan And is that causing you a problem? If it is, then you can answer your question trivially by changing it to use `GETDATE()` and benchmarking. If it is not, then this question is too open-ended for StackOverflow. – RB. Feb 13 '13 at 15:10
  • @RB you can say that about any question about optimization. – Uğur Gümüşhan Feb 13 '13 at 15:12

2 Answers2

19
SELECT SYSDATETIME();
GO
DECLARE @d DATETIME2(7) = SYSDATETIME();
GO 10000
SELECT SYSDATETIME();
GO
DECLARE @d DATETIME = SYSDATETIME();
GO 10000
SELECT SYSDATETIME();
GO
DECLARE @d DATETIME2(7) = GETDATE();
GO 10000
SELECT SYSDATETIME();
GO
DECLARE @d DATETIME = GETDATE();
GO 10000
SELECT SYSDATETIME();

Results:

  • Assigning SYSDATETIME to DATETIME2(7) : 3.4 s
  • Assigning SYSDATETIME to DATETIME : 3.3 s
  • Assigning GETDATE to DATETIME2(7) : 3.4 s
  • Assigning GETDATE to DATETIME : 3.3 s

So it appears to not matter. What matters is what type of variable you assign it to, and even that is not by much. 10000/0.1 seconds means the delta is very, very small and not enough to worry about. I would rather be consistent in this case.

Uğur Gümüşhan
  • 2,455
  • 4
  • 34
  • 62
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • 5
    Aaron, you have to admit that first sentence is a little harsh. I don't know that we need to chide the poor guy. – Brad Larson Feb 13 '13 at 23:28
  • 2
    @Brad sorry Brad, just getting tired of "which is faster, A or B?" questions that waste more people's time than actually testing it themselves. I don't think it's the OP's job to edit my answer - if he was offended by it, flag it. – Aaron Bertrand Feb 13 '13 at 23:30
  • 1
    Brad, I have to agree with @AaronBertrand. If the OP found it offensive he could have flagged it. That's what it's there for right? – Kermit Feb 13 '13 at 23:35
  • this answer + string of comments is hilarious...brightened up my morning no end (: please don't pick on me though!) – whytheq Aug 16 '13 at 10:12
6

In a few extermely poorly designed tests, on my machine it appears that SYSDATETIME() may be faster:

select sysdatetime()
go
declare @Dt datetime
select @dt = sysdatetime()
select @dt = DATEADD(day,1,@dt)
go 15000
select sysdatetime()
go
declare @Dt datetime
select @dt = GETDATE()
select @dt = DATEADD(day,1,@dt)
go 15000
select sysdatetime()
go

On my machine, this is tending to produce a gap of ~1 second between the first two result sets and ~2 seconds between the 2nd and 3rd. Reversing the order of the two tests reverses the gaps.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • I get the opposite. `sysdatetime` takes c. 4.3 seconds, and `getdate` takes c. 4.0 seconds. Obviously my machine is much slower than yours as well! – RB. Feb 13 '13 at 15:19
  • what's the point in assigning sysdateime to datetime rather than datetime2(7) – Uğur Gümüşhan Feb 13 '13 at 15:44
  • @UğurGümüşhan - the point of the exercise was to write a trivial code snippet where the only difference between the two was whether we called `SYSDATETIME` or `GETDATE()` (since that was your posited question). If anything, you might expect the above to penalize us with an additional conversion being required, but it does not appear to. – Damien_The_Unbeliever Feb 13 '13 at 15:46