CODEWITHSUNDEEP
sql-serversql-update
Michael Miner
Michael Miner

Reputation: 964

SQL Update Performance

A quick question about the performance of an update statement in SQL. Assuming I have 1 million rows in the table and I want to update a date column on half of them.

My update is 

UPDATE Table SET DATE = Date.dbo.fn_GetAlwaysUTCDate() WHERE (HALF THE ROWS)

In the above query, will the function Date.dbo.fn_GetAlwaysUTCDate() be called 500k times? I know that if it were in a WHEREit would be called on each row as it is not a deterministic function in the eyes of the compiler/optimizer.

Upvotes: 1

Views: 137

Answers (2)

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

Obviously storing datetime in a variable will be always faster: Considering the execution plan:

For the queries : 

update colname set dt = dbo.fn_getutcdate()

declare @dt datetime = dbo.fn_getutcdate()

update colname set dt = @dt

enter image description here

Eventhough percentage wise it is not much of different, but each every record compute scalar will execute separately which will be costly operation and keep that value in tablespool and then further update

Upvotes: 0

Hiten004
Hiten004

Reputation: 2481

Please try this. This way function call only one time. 

Declare @dt datetime = Date.dbo.fn_GetAlwaysUTCDate()

UPDATE Table SET DATE = @dt WHERE (HALF THE ROWS)

Upvotes: 2

Related Questions