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?

Use `option (recompile)` to allow the value of the variables to be sniffed not guessed. If you still see a difference in the plans [see this](http://stackoverflow.com/questions/18241977/query-runs-slow-with-date-expression-but-fast-with-string-literal) — Martin Smith, Feb 27 '14 at 08:03
Thanks. The option (recompile) didn't improve estimation plan, but it improved the query time ... and that's the important point. If you add it as answer, I will mark it as accepted. — jing, Feb 27 '14 at 09:09
@jing - You need to look at the **actual** execution plan, not estimated. In the estimated no `SELECT @runtime = '2014-02-15 03:34:17'` assignment is ever made so the estimates for the second plan still won't use that. — Martin Smith, Feb 27 '14 at 11:35
The execution plans comparison confirmed suggestions: Without option(recompile) it performed Expensive Clustered Index Scan where it worked with unspecified @runtime. The faster query used Index Seek for exact dates. Thanks for solution. — jing, Feb 28 '14 at 14:05

score 4 · Accepted Answer · answered Feb 27 '14 at 11:45

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.

score 0 · Answer 2 · answered Feb 27 '14 at 10:26

0

A function on the left side of the comparison is like a black box to SQL Server. You always have to try to move the function to the right

The "between" keyword is added for convenience for the developer. The query optimizer always rewrites this to double comparison. Between isn't slower than double comparison. You can see this in action when you use: SET STATISTICS PROFILE ON at the top of your query enter image description here

answered Feb 27 '14 at 10:26

Eelco Drost

407
3
2

I don't understand this answer at all. Where a function appears (left or right of, you haven't said, but presumably the `=` sign of a comparison) doesn't make a blind bit of difference. And even if it did, in the OPs query, the function calls are to the right hand side, which you claim is the preferred location. – Damien_The_Unbeliever Feb 27 '14 at 11:40

score -1 · Answer 3 · answered Feb 27 '14 at 08:26

-1

A query execution time depends on many factors.

More, in this case, doing operations on WHERE clause, for each tuple, it's normal to be a little slow. My suggetion is to tru to improve your select. 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 double comparison (>= , <=).

answered Feb 27 '14 at 08:26

Narcis

76
4

As I mentioned in question, I tried it, but it didn't help. I tried also > < instead of BETWEEN, but result was the same. – jing Feb 27 '14 at 08:58

Why DATEADD slows down SQL query?

3 Answers3