Geek Answers Handbook

Adding 0 minutes with DATEADD

I'm looking for some performance tuning options in my database, and I came across a select clause with this where clause:

WHERE GETDATE() > DATEADD(mi,0,[TimeStamp])

My question is, does it make sense to use DATEADD this way? I don't understand why a developer would not just use this instead:

 WHERE GETDATE() > [TimeStamp]
+7
sql sql-server
source share
2 answers

[1] WHERE GETDATE() > [TimeStamp] | WHERE Expression > Column | WHERE Column < Expression are SARG predicates, and this means that a DBMS (such as SQL Server) can use Index Seek (or Index Seek + Key|RID Lookup ) to quickly find and return the required rows.

[2] WHERE GETDATE() > DATEADD(mi,0,[TimeStamp]) | WHERE Expression > ScalarFunction(Column) | WHERE ScalarFunction(Column) < Expression are not SARG-capable predicates, and this means that even if there is a valid index on [Timestamp] , the DBMS will not be able to use Seek . Instead, the operator Table|Index|Clustered Scan will be used, which has (usually , but not always ) lower performance than Index Seek (at least for OLTP systems).

So, DATEADD(mi,0,[TimeStamp]) forcibly uses the Scan data access operator, which is used to draw up a execution plan, even if there is an appropriate index. Without DATEADD DBMS can use the Seek operator, which might not be the optimal choice for some / most query parameter values. *

I would test both solutions (with and without DATEADD(MINUTE, 0, ...) ) to see if there are any differences in performance.

Note # 1: To force SQL2008R2 to scan, enter the FORCESCAN table hint (SQL2008 also comes with the FORCESEEK table FORCESEEK ) ( links ).

Note # 2: Basically, this function, used in the [Timestamp] column ( DATEADD(mi,0,[TimeStamp]) ), will also have consequences for optimizing query / query compilation, since no column statistics can be used. Instead, since the operation is not = , the predefined selectivity will be 33% (as far as I remember from the video presentation, it is not official / from documents).

+4
source share

I assume the developer was wrong when writing this code.

DATEADD can slow performance because indexes will not be used, and the author was not worried about performance https://www.sqlservercentral.com/Forums/608017/dateAdd-inside-where-clause

If the filter value can change, it’s better to write

 [TimeStamp] < DATEADD(MINUTE, 0, GETDATE())

In this case, you handle not only cases when you add 0 minutes, but others ( DATEADD(MINUTE, 10, GETDATE()) for example)

0
source share

More articles:


All Articles