andba77 andba77 - 11 months ago 34
SQL Question

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]

Answer Source

[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).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download