Why DATEADD slows down SQL query?
performance, sql-server, t-sql
Solution
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.
Problem
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?