SQL 更新性能 [英] SQL Update Performance

问题描述

一个关于 SQL 中更新语句性能的快速问题.假设表中有 100 万行，我想更新其中一半的日期列.

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.

我的更新是

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

在上面的查询中，函数 Date.dbo.fn_GetAlwaysUTCDate() 会被调用 50 万次吗?我知道如果它在 WHERE 中，它将在每一行上调用，因为它在编译器/优化器眼中不是确定性函数.

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.

推荐答案

显然将日期时间存储在变量中总是会更快:考虑执行计划:

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

对于查询:

update colname set dt = dbo.fn_getutcdate()

declare @dt datetime = dbo.fn_getutcdate()

update colname set dt = @dt

尽管在百分比方面并没有太大的不同，但每个记录计算标量将单独执行，这将是昂贵的操作并将该值保存在表池中，然后进一步更新

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

这篇关于SQL 更新性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！