13

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

I'm currently doing this:

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

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

Anything that is older than 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?

Per von Zweigbergk
  • 189
  • 11
Murtaza Mandvi
  • 10,708
  • 23
  • 74
  • 109

1 Answers1

20

The expression

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

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

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)

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

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • 1
    I think the condition should be WHERE senddate < dateadd(dd, -@CalculationInterval, @RunDate) with the condition WHERE senddate > dateadd(dd, @CalculationInterval, @RunDate) for @RunDate= '2013-01-11' and @CalculationInterval= 10 we delete all rows with senddate>'2013-01-21' when I think we would like to delete all rows with senddate<'2013-01-01' – Krystian Lieber Jan 24 '13 at 16:19
  • @Krystian Lieber - Correct I already made that change on my end, just wanted to get an idea of how to make it sargable :) – Murtaza Mandvi Jan 24 '13 at 16:37
  • @Mitch Wheat can you correct that answer as per above suggestion and I will mark it as accepted? – Murtaza Mandvi Jan 24 '13 at 16:38
  • I doesn't know that '-@var' = '-1*var'. I learned two things on 1 answer :D thanks! – FabianSilva Sep 23 '14 at 14:50