在很多地方使用 GETDATE() 时，使用变量是否更好? [英] When using GETDATE() in many places, is it better to use a variable?

问题描述

更好，我的意思是它是否以非边际的方式提高了性能?

By better, I mean does it improve performance by some non-marginal amount?

也就是说，每次我调用 GETDATE() 时，服务器做了多少工作来返回那个值?

That is to say, each time I call GETDATE(), what amount of work does the server do to return that value?

如果我在存储过程的很多地方使用 GETDATE()，我应该创建一个变量来存储交易日期吗?

If I'm using GETDATE() in many places in a stored procedure, should I instead be creating a variable to store the date of the transaction?

declare @transDate datetime = GETDATE()

基准数据会很棒.

编辑 我想澄清一下:我主要感兴趣的是这两种可能性之间的实际性能差异，以及它是否显着.

EDIT I want to clarify: I'm interested mainly in the actual performance differences between these two possibilities, and whether or not it is significant.

推荐答案

[注意:如果您要否决此答案，请发表评论解释原因.它已经被否决了很多次，最后 ypercube(谢谢)至少解释了一个原因.我无法删除答案，因为它已被接受，因此您不妨帮助改进它.]

[NOTE: If you are going to downvote this answer, please leave a comment explaining why. It has already been downvoted many times, and finally ypercube (thank you) explained at least one reason why. I can't remove the answer because it is accepted, so you might as well help to improve it.]

根据微软的这一交流，GETDATE() 从查询中的常量切换到 SQL Server 2005 中的非确定性.回想起来，我认为这并不准确.我认为它在 SQL Server 2005 之前是完全非确定性的，然后自 SQL Server 2005 以来被黑进了一种叫做非确定性运行时常量"的东西.后面的短语似乎真的意味着查询中的常量".

According to this exchange on Microsoft, GETDATE() switched from being constant within a query to non-deterministic in SQL Server 2005. In retrospect, I don't think that is accurate. I think it was completely non-deterministic prior to SQL Server 2005 and then hacked into something called "non-deterministic runtime constant" since SQL Server 2005". The later phrase really seems to mean "constant within a query".

(和GETDATE() 被定义为明确且自豪的非确定性，没有限定符.)

(And GETDATE() is defined as unambiguously and proudly non-deterministic, with no qualifiers.)

唉，在 SQL Server 中，非确定性并不意味着对每一行都计算一个函数.SQL Server 确实使这件事变得不必要地复杂和模棱两可，而且关于该主题的文档很少.

Alas, in SQL Server, non-deterministic does not mean that a function is evaluated for every row. SQL Server really does make this needlessly complicated and ambiguous with very little documentation on the subject.

实际上，函数调用是在查询运行时计算的，而不是在查询编译时计算一次，每次调用时其值都会更改.在实践中，GETDATE() 只为每个使用它的表达式计算一次——在执行时间而不是编译时间.但是，Microsoft 将 rand() 和 getdate() 归入一个特殊类别，称为非确定性运行时常量函数.相比之下，Postgres 不会跳过这样的圈套，它只是调用在稳定"状态下执行时具有恒定值的函数.

In practice the function call is evaluated when the query is running rather than once when the query is compiled and its value changes each time it is called. In practice, GETDATE() is only evaluated once for each expression where it is used -- at execution time rather than compile time. However, Microsoft puts rand() and getdate() into a special category, called non-deterministic runtime constant functions. By contrast, Postgres doesn't jump through such hoops, it just calls functions that have a constant value when executed as "stable".

尽管 Martin Smith 发表了评论，但 SQL Server 文档在这个问题上并没有明确说明 -- GETDATE() 被描述为非确定性"和非确定性运行时常量"，但该术语不是真没解释.我发现这个词的地方例如，文档中的下一行说不要在子查询中使用非确定性函数.对于非确定性运行时常量"，这将是愚蠢的建议.

Despite Martin Smith's comment, SQL Server documentation is simply not explicit on this matter -- GETDATE() is described as both "nondeterministic" and "non-deterministic runtime constant", but that term isn't really explained. The one place I have found the term , for instance, the very next lines in the documentation say not to use nondeterministic functions in subqueries. That would be silly advice for "nondeterministic runtime constant".

我建议即使在查询中也使用带有常量的变量，这样您就有了一致的值.这也使意图非常明确:您需要查询中的单个值.在单个查询中，您可以执行以下操作:

I would suggest using a variable with a constant even within a query, so you have a consistent value. This also makes the intention quite clear: You want a single value inside the query. Within a single query, you can do something like:

select . . . 
from (select getdate() as now) params cross join
     . . . 

实际上，这是一个建议，应该在查询中只计算一次，但可能有例外.出现混淆是因为 getdate() 在所有不同的行上返回相同的值——但它可以在不同的列中返回不同的值.每个带有 getdate() 的表达式都是独立计算的.如果你运行，这很明显:

Actually, this is a suggestion that should evaluate only once in the query, but there might be exceptions. Confusion arises because getdate() returns the same value on all different rows -- but it can return different values in different columns. Each expression with getdate() is evaluated independently. This is obvious if you run:

select rand(), rand()
from (values (1), (2), (3)) v(x);

在存储过程中，您可能希望变量中有单个值.如果在午夜过去时运行存储过程，并且日期发生变化，会发生什么情况?这对结果有什么影响?

Within a stored procedure, you would want to have a single value in a variable. What happens if the stored procedure is run as midnight passes by, and the date changes? What impact does that have on the results?

至于性能，我的猜测是日期/时间查找最少，并且当查询开始运行时，每个表达式都会发生一次查询.这不应该是一个真正的性能问题，而更像是一个代码一致性问题.

As for performance, my guess is that the date/time lookup is minimal and for a query occurs once per expression as the query starts to run. This should not really a performance issue, but more of a code-consistency issue.

这篇关于在很多地方使用 GETDATE() 时，使用变量是否更好?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！