2

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?

O. Jones
  • 103,626
  • 17
  • 118
  • 172
CodeControl
  • 48
  • 3
  • you are worrying about optimisation in the the wrong place. – Mitch Wheat Feb 23 '21 at 10:02
  • 2
    That calculation doesn't appear to depend on any row data. That means, within the query in which it's used, it should be calculated once and treated as a constant, unless the query optimizer is having a very off day. Generally, trust the optimizer, and remember that in SQL, you tell the system *what you want*, not *how to do it*. – Damien_The_Unbeliever Feb 23 '21 at 10:13
  • In fact, such operations on constants are probably going to be calculated during the compilation phase so there might be no effect on execution at all. It also seems odd that `addMinutes()` in EF accepts a floating point value, when the underlying function requires an integer. – Gordon Linoff Feb 23 '21 at 12:34

1 Answers1

2

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.
O. Jones
  • 103,626
  • 17
  • 118
  • 172