Datediff性能 [英] Datediff performance

问题描述

我有一个天数变量，我想与datetime列（senddate）进行比较。

I have a number of days variable which I want to compare against a datetime column (senddate) .

我目前正在这样做：

DECLARE @RunDate datetime = '2013-01-01' 
DECALRE @CalculationInterval int = 10

DELETE
FROM TableA
WHERE datediff(dd, senddate, @RunDate) > @CalculationInterval 

所以基本上任何比10天更旧的东西都应该删除，我们在sendDate列上有Index但是速度仍然要慢得多，我知道出于性能原因，左侧不应该进行计算，但是解决该问题的最佳方法是什么？

So basically anything that is older then 10 days should get deleted, we have Index on sendDate column but still the speed is much slower, I know the left side should not have calculation for performance reasons, but what is the optimal way of otherwise solving this issue?

推荐答案

表达式

WHERE datediff(dd, senddate, @RunDate) > @CalculationInterval 

将无法在 senddate上使用索引列，因为LHS在发送日期

won't be able to use an index on the senddate column, because of the function on the LHS on senddate

上具有此功能 WHERE 子句 SARGable（即能够使用索引），更改为等效条件：

In order to make the WHERE clause 'SARGable' (i.e. able to use an index), change to the equivalent condition:

WHERE senddate < dateadd(dd, -@CalculationInterval, @RunDate)

[感谢@Krystian Lieber，指出条件不正确]。

[Thanks to @Krystian Lieber, for pointing out incorrect condition ].

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