CODEWITHSUNDEEP
sql-serverperformancet-sql
jing
jing

Reputation: 2021

Why DATEADD slows down SQL query?

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?

Upvotes: 9

Views: 3333

Answers (3)

Martin Smith
Martin Smith

Reputation: 453028

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.

Upvotes: 4

Eelco Drost
Eelco Drost

Reputation: 415

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 queryenter image description here

Upvotes: 0

Narcis
Narcis

Reputation: 76

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 (>= , <=).

Upvotes: -1

Related Questions