I'm looking at some opportunities for performance tuning in my database and I've come across a select statement with this where clause:
WHERE GETDATE() > DATEADD(mi,0,[TimeStamp])
My question is, does it ever make sense to use DATEADD in this fashion? I don't understand why the developer wouldn't simply use this instead:
WHERE GETDATE() > [TimeStamp]
We can use DATEADD() function like below to add minutes to DateTime in Sql Server. DATEADD() functions first parameter value can be minute or mi or n all will return the same result.
You can use the DateAdd function to add or subtract a specified time interval from a date. For example, you can use DateAdd to calculate a date 30 days from today or a time 45 minutes from now. To add days to date, you can use Day of Year ("y"), Day ("d"), or Weekday ("w").
[1] WHERE GETDATE() > [TimeStamp]
| WHERE Expression > Column
| WHERE Column < Expression
are SARG-able predicates and this means that DBMS (ex. SQL Server) could use Index Seek
(or Index Seek + Key|RID Lookup
) for execution plan in order to quickly find and return required rows.
[2] WHERE GETDATE() > DATEADD(mi,0,[TimeStamp])
| WHERE Expression > ScalarFunction(Column)
| WHERE ScalarFunction(Column) < Expression
are not SARG-able predicates and this means that even if there is a proper index on [Timestamp]
DBMS will not be able to use Seek
. Instead a Table|Index|Clustered Scan
operator will be used which have (generally speaking but not always) lower performance than Index Seek
(at least for OLTP systems).
So DATEADD(mi,0,[TimeStamp])
forces a Scan
data access operator to be used when execution plan is generated even if there is a proper index. Without DATEADD
DBMS could use an Seek
operator that could not be optimal choice for some/most of values of query parameters.*
I would test both solutions (with and without DATEADD(MINUTE, 0, ...)
) to see if there are any differences in terms of performances.
Note #1: In order to force an scan SQL2008R2 introduced FORCESCAN
table hint (SQL2008 comes also with FORCESEEK
table hint)(references).
Note #2: Basically this function applied on [Timestamp]
column (DATEADD(mi,0,[TimeStamp])
) will have also implications during query compilation / query optimization because no column statistics can be used. Instead because operation is not =
the predefined selectivity used will be 33% (as far I remember from a video presentation - it's not official / from docs).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With