Why is DATEADD slowing an SQL query?

Question

In my SQL Server query, I am trying to get a 2 second data range:

DECLARE @runtime AS datetime SELECT @runtime = '2014-02-15 03:34:17' SELECT Application FROM commandcip WHERE commandname = 'RunTestCase' AND (createdate BETWEEN DATEADD(s, -1, @runtime) AND DATEADD(s, 1, @runtime)) 

This command works very slowly, it takes minutes, and the estimated cost of subtitles based on the performance analyzer is 2800.

On the other hand, if I calculate the range manually, the request will be very fast (Estimated subtitle cost = 0.5, request time <1 second):

 SELECT Application FROM commandcip WHERE commandname = 'RunTestCase' AND createdate BETWEEN '2014-02-15 03:34:16' AND '2014-02-15 03:34:18' 

I confirmed that both commands return the correct data. I confirmed that my DATEADD commands DATEADD returning the correct dates. I also tried getting DATEADD one step earlier (in separate variables @mindate , @maxdate ), but that didn't help.

How to speed up the first query without manually calculating the range?

Answer 1

For createdate BETWEEN '2014-02-15 03:34:16' AND '2014-02-15 03:34:18' the column values ​​can be viewed in the column statistics to estimate the number of rows that will correspond.

Variable values ​​are not sniffed, unless you use option (recompile) , so SQL Server will simply use heuristics to guess the number.

Presumably, the plan obtained from using the first number is different from the plan for using the second number.

eg. One estimates fewer rows and uses a non-covering search index, and the other uses a full scan, since the approximate number of rows is above the tipping point , where this option is considered cheaper.

Answer 2

The function on the left side of the comparison is like a black box on SQL Server. You always need to try moving the function to the right.

The key "between" was added for the convenience of the developer. The query optimizer always rewrites this for a double comparison. Between is no slower than a double comparison. You can see this in action when you use: SET STATISTICS PROFILE At the top of your query

Answer 3

The query execution time depends on many factors.

In this case, doing operations on the WHERE clause for each tuple is normal to be a little slow. My suggestion is to improve your choice. For example, add 2 variables, @start datetime = DATEADD (s, -1, @runtime), @end datetime = DATEADD (s, 1, @runtime) and replace DATEADD (s, -1, @runtime) and DATEADD (s , 1, @runtime). Another, sometimes between, is slower than a double comparison (> =, <=).