GetDate() or CURRENT_TIMESTAMP

Question

GetDate() or CURRENT_TIMESTAMP

Viewing 15 posts - 16 through 30 (of 52 total)

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

grahamc SSCertifiable Points: 6762 More actions · Answer 1

GETDATE() for no other reason than habit.... and the "odd" timestamp column.

GSquared SSC Guru Points: 260824 More actions · Answer 2

vinothraj (12/17/2010)
I'm working in linked server but you might be working in your own. i just told it based upon the average of ten times but i would like to convey it seems to be current_timestamp conceives less time.

They use exactly the same code in the engine. Any perceived difference is due to flaws in testing methodology.

Here's a test I just ran. The only change between the runs is which Select line is commented.

DECLARE @Start DATETIME;

SELECT @Start = GETDATE();

IF OBJECT_ID(N'tempdb..#T') IS NOT NULL

DROP TABLE #T;

SELECT CURRENT_TIMESTAMP AS DT

--SELECT GETDATE() AS DT

INTO #T

FROM dbo.Numbers;

SELECT DATEDIFF(millisecond, @Start, GETDATE());

/*

milliseconds of runtime

getdate()

146

10

16

13

10

current_timestamp

10

13

10

20

*/

One very noticeable thing about this is that I ran GETDATE() first, and it took 146 milliseconds to handle 10,000 iterations, on a low-power VM. After that first run, caching caused subsequent runs to be much faster.

And, the first time I ran it with CURRENT_TIMESTAMP, it obviously used the cached execution and definitions from the GETDATE() version. This is because it's the same code for both. You'll also note that, once the Numbers table was cached in RAM, the run-times are statistically identical. Any variation is definitely within margin-of-error.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

vinothraj (12/17/2010)
I'm working in linked server but you might be working in your own. i just told it based upon the average of ten times but i would like to convey it seems to be current_timestamp conceives less time.

Sounds to me like your testing is skewed. You have a lot of network and display overhead included.

tommyh SSCertifiable Points: 6302 More actions · Answer 4

GSquared (12/17/2010)
vinothraj (12/17/2010)
I'm working in linked server but you might be working in your own. i just told it based upon the average of ten times but i would like to convey it seems to be current_timestamp conceives less time.
They use exactly the same code in the engine. Any perceived difference is due to flaws in testing methodology.
Here's a test I just ran. The only change between the runs is which Select line is commented.
DECLARE @Start DATETIME;
SELECT @Start = GETDATE();
IF OBJECT_ID(N'tempdb..#T') IS NOT NULL
DROP TABLE #T;
SELECT CURRENT_TIMESTAMP AS DT
--SELECT GETDATE() AS DT
INTO #T
FROM dbo.Numbers;
SELECT DATEDIFF(millisecond, @Start, GETDATE());
/*
milliseconds of runtime
getdate()
146
10
16
13
10
current_timestamp
10
10
13
10
20
*/
One very noticeable thing about this is that I ran GETDATE() first, and it took 146 milliseconds to handle 10,000 iterations, on a low-power VM. After that first run, caching caused subsequent runs to be much faster.
And, the first time I ran it with CURRENT_TIMESTAMP, it obviously used the cached execution and definitions from the GETDATE() version. This is because it's the same code for both. You'll also note that, once the Numbers table was cached in RAM, the run-times are statistically identical. Any variation is definitely within margin-of-error.

Excuse a noob for asking. But is SQL really calling GetDate() and CURRENT_TIMESTAMP for each row in your test?. Because if i do something similar that takes many seconds and actually return a result i get the exact same value on all rows. One would think that if SQL actually called once per row... there would be a differance between the rows.

SELECT CURRENT_TIMESTAMP AS DT

--SELECT GETDATE() AS DT

INTO #T

FROM sysobjects s1

join sysobjects s2 on 1 = 1

select dt, count(*) from #T group by dt

Returns : 2010-12-17 15:13:10.310

4372281

/T

Derrick Smith SSChampion Points: 10833 More actions · Answer 5

As gsquared said, there is no difference at all.

SQL will translate current_timestamp AND getdate() to the exact same code to be executed on the back end. There is no performance or result difference possible.

GSquared SSC Guru Points: 260824 More actions · Answer 6

Do you prefer this test?

SET NOCOUNT ON;

IF OBJECT_ID(N'tempdb..#T') IS NOT NULL

DROP TABLE #T;

IF OBJECT_ID(N'tempdb..#T2') IS NOT NULL

DROP TABLE #T2;

CREATE TABLE #T (DT DATETIME);

SELECT GETDATE() AS START INTO #T2;

go

INSERT INTO #T (DT)

SELECT GETDATE();

go 100000

SELECT DATEDIFF(millisecond, Start, GETDATE())

FROM #T2;

/*

CURRENT_TIMESTAMP

113890

113823

113026

GETDATE()

113923

113226

111956*/

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004309 More actions · Answer 7

vinothraj (12/16/2010)
I've checked elapsing time for each of those and got approximate time is less for current_timestamp than getdate().
Is current_timestamp better? i think so....

Please post the test code. Thanks.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004309 More actions · Answer 8

Heh... I use GETDATE() for several reasons but these are my favorites... It has fewer characters to type, I've used it forever, I don't believe in the myth of portability, and it ticks people like Celko off. 😛

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

fabricioaraujorj SSC Journeyman Points: 88 More actions · Answer 9

Jeff Moden (12/17/2010)
Heh... I use GETDATE() for several reasons but these are my favorites... It has fewer characters to type, I've used it forever, I don't believe in the myth of portability, and it ticks people like Celko off. 😛

Mr Moden,

I don't believe in full portability also. But since db engines started to share some commands with same syntax, to do exactly the same things, I use it instead of the proprietary syntax - and this is the case of CURRENT_TIMESTAMP. GetDate() gives me nothing as reward to use it (the typing difference is negligible). And since I don't work exclusively with SQL Server, is less a headache.

If we we're talking about UPDATE..FROM, that would be a whole other story...

Jeff Moden SSC Guru Points: 1004309 More actions · Answer 10

fabricioaraujorj (12/20/2010)
Jeff Moden (12/17/2010)
Heh... I use GETDATE() for several reasons but these are my favorites... It has fewer characters to type, I've used it forever, I don't believe in the myth of portability, and it ticks people like Celko off. 😛
Mr Moden,
I don't believe in full portability also. But since db engines started to share some commands with same syntax, to do exactly the same things, I use it instead of the proprietary syntax - and this is the case of CURRENT_TIMESTAMP. GetDate() gives me nothing as reward to use it (the typing difference is negligible). And since I don't work exclusively with SQL Server, is less a headache.
If we we're talking about UPDATE..FROM, that would be a whole other story...

Heh... I use GETDATE() just because it IS proprietary. 😉 And, yes... GetDate() does give me something as a reward... fewer characters and no bloody underscore. 😀

However... I do have an appreciation for folks that work with more than one database engine and would rather have the habit of typing whatever works across the board. 🙂

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vinothraj Ten Centuries Points: 1349 More actions · Answer 11

Jeff Moden (12/17/2010)
vinothraj (12/16/2010)
I've checked elapsing time for each of those and got approximate time is less for current_timestamp than getdate().
Is current_timestamp better? i think so....
Please post the test code. Thanks.

Here you go!!!!!

Declare @i as int

set @i = 10

if object_id('tempdb..#date') is not null

drop table #date

Create Table #date (dt datetime, type varchar(30))

while (@i > 0)

Begin

insert into #date

select getdate(), 'getdate'

Set @i = @i -1

End

Set @i = 10

while (@i > 0)

Begin

insert into #date

select current_timestamp,'ct'

Set @i = @i -1

End

select * from #date

And Result:

/*

2010-12-21 04:33:29.753getdate

2010-12-21 04:33:29.760getdate

2010-12-21 04:33:29.763ct

*/

-- In the result you can see fluctuations in getdate() where as you cant find in current_timestamp. :w00t:

Jeff Moden SSC Guru Points: 1004309 More actions · Answer 12

vinothraj (12/20/2010)
Jeff Moden (12/17/2010)
vinothraj (12/16/2010)
I've checked elapsing time for each of those and got approximate time is less for current_timestamp than getdate().
Is current_timestamp better? i think so....
Please post the test code. Thanks.
Here you go!!!!!
Declare @i as int
set @i = 10
if object_id('tempdb..#date') is not null
drop table #date
Create Table #date (dt datetime, type varchar(30))
while (@i > 0)
Begin
insert into #date
select getdate(), 'getdate'
Set @i = @i -1
End
Set @i = 10
while (@i > 0)
Begin
insert into #date
select current_timestamp,'ct'
Set @i = @i -1
End
select * from #date
And Result:
/*
2010-12-21 04:33:29.753getdate
2010-12-21 04:33:29.753getdate
2010-12-21 04:33:29.753getdate
2010-12-21 04:33:29.753getdate
2010-12-21 04:33:29.753getdate
2010-12-21 04:33:29.753getdate
2010-12-21 04:33:29.760getdate
2010-12-21 04:33:29.760getdate
2010-12-21 04:33:29.760getdate
2010-12-21 04:33:29.760getdate
2010-12-21 04:33:29.763ct
2010-12-21 04:33:29.763ct
2010-12-21 04:33:29.763ct
2010-12-21 04:33:29.763ct
2010-12-21 04:33:29.763ct
2010-12-21 04:33:29.763ct
2010-12-21 04:33:29.763ct
2010-12-21 04:33:29.763ct
2010-12-21 04:33:29.763ct
2010-12-21 04:33:29.763ct
*/
-- In the result you can see fluctuations in getdate() where as you cant find in current_timestamp. :w00t:

And yet, I can get this...

dt type

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

2010-12-21 00:49:01.610 getdate

2010-12-21 00:49:01.613 getdate

2010-12-21 00:49:01.613 ct

2010-12-21 00:49:01.617 ct

(20 row(s) affected)

The problem with such a test is that there's no guarantee that you're starting at the beginning of a 3.3 millisecond time slice. Also, the word "getdate" is longer than the word "ct" and it takes longer to store so you've given "ct" an unfair advantage.

Let's try a slightly different test with SQL Profiler running...

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

GO

--===== Test GETDATE() =====================================================================

DECLARE @Counter INT,

@Bitbucket DATETIME; --Used to take both the disk and the display out of the picture

SELECT @Counter = 10000000;

WHILE @Counter > 0

SELECT @Bitbucket = GETDATE(),

@Counter = @Counter - 1;

GO

--===== Test CURRENT_TIMESTAMP ==============================================================

DECLARE @Counter INT,

@Bitbucket DATETIME; --Used to take both the disk and the display out of the picture

SELECT @Counter = 10000000;

WHILE @Counter > 0

SELECT @Bitbucket = CURRENT_TIMESTAMP,

@Counter = @Counter - 1;

GO

Yep... you're seeing right. It tests each function 10 MILLION times each run and I did 3 runs. Each run takes about a minute on my 8 year old single CPU 1.8GHz machine. The code will likely run much faster on a newer machine.

Here are the results from SQL Profiler. Remember... 10 MILLION rows each. I don't know about you but I can't really see a difference there especially when each takes turns winning and losing. Then there's that insane rowcount I used in the test.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 13

Yes, GetDate() and CURRENT_TIMESTAMP are both implemented as the intrinsic function getdate, so at executin time there is absolutely no difference whatsoever. Not even a little bit.

Whether the function is evaluated once per row, or just once, depends on the query plan, internal engine details, and whatever is meant by 'once per row'...once per row at which stage of the query plan? Even if that is somehow defined, internal engine optimizations and behaviours may mean the scalar is evaluated a different number of times from anything that is shown publicly. Different query plan shapes (all of which produce the correct result set, of course) might end up calling the function a different number of times.

SQL Server generally makes no guarantees about how many times a scalar is evaluated in a query, so it's best not to make any assumptions.

There are hints, for the interested, but these are not to be mistaken for guarantees. For example, in the following plan extract, the intrinsic call to getdate appears as a run-time constant, so the function call is extracted from the tree and evaluated once, before execution starts:

<ComputeScalar>

<DefinedValues>

<DefinedValue>

<ColumnReference Column="Expr1017" />

<ScalarOperator ScalarString="getdate()">

<Identifier>

<ColumnReference Column="ConstExpr1018">

<ScalarOperator>

<Intrinsic FunctionName="getdate" />

</ScalarOperator>

</ColumnReference>

</Identifier>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</ComputeScalar>

Notice the ConstExprxxxx column reference - this often indicates a run-time constant is being used. Whether SQL Server extracts a function and evaluates it once as a runtime constant depends on its semantic analysis of your query, and the behaviour isn't always very consistent. There are a number of 'bugs' reported on Connect that all boil down to how many times SQL Server decides to evaluate a non-deterministic function like getdate or newid.

As far as preferring one over the other is concerned: meh. I don't really like either of them, in all honesty. There are any number of small considerations that differentiate the two - for example there is no equivalent of GETUTCDATE() - but it's hard to care much.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

spaghettidba SSC Guru Points: 105732 More actions · Answer 14

SQLkiwi (12/21/2010)
Notice the ConstExprxxxx column reference - this often indicates a run-time constant is being used.

Conor Cunningham has a nice blog post on Runtime Constants:

http://blogs.msdn.com/b/conor_cunningham_msft/archive/2010/04/23/conor-vs-runtime-constant-functions.aspx

-- Gianluca Sartori

Paul White SSC Guru Points: 150467 More actions · Answer 15

Gianluca Sartori (12/21/2010)
Conor Cunningham has a nice blog post on Runtime Constants:
http://blogs.msdn.com/b/conor_cunningham_msft/archive/2010/04/23/conor-vs-runtime-constant-functions.aspx

He has covered the subject (fairly lightly) more than once:

http://www.sqlskills.com/BLOGS/CONOR/post/Wrapping-my-head-around-RAND()-in-SQL-Server.aspx

http://www.sqlskills.com/blogs/conor/post/RAND()-and-other-runtime-constant-functions-redux.aspx

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi