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])
WHERE GETDATE() > [TimeStamp]
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.
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).
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).