SQL performance: does MSSQL optimise the use of date time functions or is it better to pass it as a parameter

Question

I've written this rather simple query in which I'm selecting the records that are less than 5 minutes old, based on a DateTimeOffset column called LastActivity.

I'm using EF and when inspecting the actual query I see that EF actually translates this condition: LastActivity > DateTimeOffset.UtcNow.AddMinutes(-5); to a query using the SQL datetime functions: [s].[LastActivity] > DATEADD(minute, CAST(-5.0E0 AS int), CAST(SYSUTCDATETIME() AS datetimeoffset)).

As you can see, it does some unneccessary casting (e.g. .AddMinutes expects a double), so I was wondering if it would be more performant to actually calculate the DateTime first in code and then pass the result to the query as a parameter. I know this would then depend on the statistics and I can't really say how these values will be distributed yet... I've run both queries on a sample database and there is no real difference in performance, but when the dataset increases I assume this might change.

My question is: am I correct in assuming that when there are no parameters (but using DATEADD), SQL will always use the same query plan or will it somehow optimise it because we are using SYSUTCDATETIME?

Answer 1

What EF gave you for that query will work fine, even if it's a bit of unexpected syntax.

Why?

1. WHERE timestampcolumn > DATEADD(minute, number, something_meaning_now) is a sargeable filtering term: it can use an index on timestampcolumn.
2. SYSUTCDATETIME() is a non-deterministic function. That means SQL Server knows its return value is based on something besides its input values.

So, here's what's happening: SQL Server is calculating the date "in code" before using it, just like you might do in your code. Because SQL Server knows the computed date will change (because it's non-deterministic) every time the query is used, its cached execution plan won't bind that date to a constant, so the query cache won't bloat. It would be bound if your filter were timestampcolumn < DATEADD(minute, number, '2021-01-23 12:34').

I've done tonnage of this kind of thing in large scale production and it works correctly.

You asked about scaling up. The way to do that is to put an index on your s.LastActivity column. But, to figure out what indexes you need ...

1. Use SSMS
2. Choose Show Actual Query Plan.
3. Run the query
4. Look at the query plan. It will show a recommended index if you need one.