Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Adding 0 Minutes with DATEADD

Tags:

sql

sql-server

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]
like image 360
andba77 Avatar asked Jun 20 '17 13:06

andba77


People also ask

How can I add 15 minutes to time in SQL Server?

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.

What does Dateadd function do?

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 Answers

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

like image 179
Bogdan Sahlean Avatar answered Sep 29 '22 17:09

Bogdan Sahlean