Why DATEADD slows down SQL query?

Question

In my SQL Server query I try to get 2 seconds range of data:

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 is extremely slow, it takes minutes and the Estimated Subtree Cost based on Performance analyzer is 2800.

On other hand if I compute the range manually, the query is perfectly fast (Estimated Subtree Cost = 0.5, query 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 verified that both commands return correct data. I verified that my DATEADD commands return correct dates. I also tried to get DATEADD one step sooner (into separate variables @mindate, @maxdate), but it didn't help.

How should I speedup first query without manually computing the range?

Answer 1

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

The values of variables are not sniffed except if you use option (recompile) so SQL Server will just use heuristics to guess a number.

Presumably the plan that is derived from using the first number is different from that from using the second number.

e.g. One estimates fewer rows and uses a non covering index with lookups and the other a full scan as the estimated number of rows is above the tipping point where this option is considered cheaper.