Adding 0 Minutes with DATEADD
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]
[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).
I assume that developer made a mistake when write this code.
DATEADD can decrease perfomance because indexes will not be used and author did not worry about perfomance https://www.sqlservercentral.com/Forums/608017/dateAdd-inside-where-clause
If 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 another ones ( DATEADD(MINUTE, 10, GETDATE()) for example)
上一篇: 如何启动和打开电子邮件客户端React
下一篇: 用DATEADD添加0分钟