SQL Server 是否在选择查询中优化 DATEADD 计算? [英] Does SQL Server optimize DATEADD calculation in select query?

问题描述

我在 Sql Server 2008 上有一个这样的查询:

I have a query like this on Sql Server 2008:

DECLARE @START_DATE DATETIME
SET @START_DATE = GETDATE()

SELECT * FROM MY_TABLE
WHERE TRANSACTION_DATE_TIME > DATEADD(MINUTE, -1440, @START_DATE)

在您在上面看到的选择查询中，SqlServer 是否对查询进行了优化，以免一次又一次地计算 DATEADD 结果.还是我自己负责将 DATEADD 结果存储在临时变量上?

In the select query that you see above, does SqlServer optimize the query in order to not calculate the DATEADD result again and again. Or is it my own responsibility to store the DATEADD result on a temp variable?

推荐答案

令人惊讶的是，我发现使用 GETDATE() 内联似乎比预先执行此类计算更有效.

Surprisingly, I've found that using GETDATE() inline seems to be more efficient than performing this type of calculation beforehand.

DECLARE @sd1 DATETIME, @sd2 DATETIME;
SET @sd1 = GETDATE();

SELECT * FROM dbo.table
WHERE datetime_column > DATEADD(MINUTE, -1440, @sd1)

SELECT * FROM dbo.table
WHERE datetime_column > DATEADD(MINUTE, -1440, GETDATE())

SET @sd2 = DATEADD(MINUTE, -1440, @sd1);

SELECT * FROM dbo.table
WHERE datetime_column > @sd2;

如果您检查这些计划，中间查询将始终以最低的成本出现(但并不总是最低的经过时间).当然，这可能取决于您的索引和数据，您不应该基于一个查询做出任何假设，即相同的抢先优化将适用于另一个查询.我的直觉是不执行任何内联计算，而是使用上面的 @sd2 变体......但我了解到我不能一直相信我的直觉，我不能根据我在特定场景中所经历的行为做出一般假设.

If you check the plans on those, the middle query will always come out with the lowest cost (but not always the lowest elapsed time). Of course it may depend on your indexes and data, and you should not make any assumptions based on one query that the same pre-emptive optimization will work on another query. My instinct would be to not perform any calculations inline, and instead use the @sd2 variation above... but I've learned that I can't trust my instinct all the time and I can't make general assumptions based on behavior I experience in particular scenarios.

这篇关于SQL Server 是否在选择查询中优化 DATEADD 计算?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！