0

My query is running a little slow and I think it's because of the where datediff function.

WHERE DateDiff(minute, TB1.stDate, getdate()) > 50

(With this approach at least is using the index I created)

it was before that something like this:

datediff(minute, TB1.stDate), '2017-01-01') <= 0)

I would like to understand if there's a better way to filter with datediff.

My question is, how is my query already using the index, even when inside the DATEDIFF function.

10

Never apply functions to columns, it means no index can be used for a seek and makes cardinality estimation very hard. Saying "using the index" doesn't give a good picture of how or if that is even worthwhile.

First, to avoid applying a function to a column...

WHERE DateDiff(minute, TB1.stDate, getdate()) > 50

...can be rewritten as...

WHERE TB1.stDate < DATEADD(MINUTE, -50, GETDATE());

Your original query said "find any row where the difference between the date in the table and now is greater than 50 minutes" which translates to "find any row where the date in the table is older than 50 minutes ago." So, I subtracted 50 minutes from now, and queried for date values older than that. Another more detailed answer here.

I suspect your index is getting scanned because it covers the query (you can get better than a guess if you include the index definition, the rest of the query, and the execution plan).

0
-3

I had wondered whether it would be even quicker if that

DATEADD(MINUTE, -50, GETDATE()) 

clause was a pre-computed SQL variable?

DECLARE @threshold DATETIME = DATEADD(MINUTE, -50, GETDATE());

...
WHERE TB1.stDate < @threshold

I have since been educated by Erik Darling and Aaron Bertrand that using a variable is often worse than using the expression! Thanks to both of them! I will leave this reply here in case the replies to it can help anyone else in the future. Definitely read their replies!

3
  • 3
  • 2
    SQL Server is smart enough to not recalculate the value for every row, so unless you want to keep the expression out of the query for code maintenance or readability concerns, or because you use it in 15 places, and those outweigh the points in Erik's link, there is no gain in stuffing the result of the expression in a variable. Sep 10 at 12:12
  • Thank you both for the education - I will update my comment.
    – CPM
    Sep 10 at 22:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.