13

I have a query like this:

SELECT col1
FROM   MyTable
WHERE  
    DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) 
       BETWEEN col2 
       AND     col3
;

This gives a tooltip on the execution plan similar to this:

Execution Tooltip

Does the dateadd part of the seek predicates get executed for every row in the query? Or does SQL Server calculate the value once for the whole query?

2 Answers 2

20
+100

Certain functions that are known to be runtime constants go through the process called constant folding. When 'folding' a constant, an expression is evaluated early in the query execution, the result is cached and the cached result used instead when needed. The expression in your query DATEADD(dd, 0, DATEDIFF(dd, 0, getdate())) is, AFAIK, a runtime constant and thus will be folded and evaluated only once per query.

As trivia: the RAND() function that one would expect to be unfoldable is actually foldable, which leads to some unexpected behavior. But others, for instance NEWID(), are not foldable and will force an evaluation per row.

1
3

Execution plans are great but sometimes they just don't tell you the truth. So here is a proof based on performance test.

(and the bottom line - the expression is not being evaluated for every row)


;with t(i) as (select 0 union all select i+1 from t where i < 9)
select getdate()-1 as col1,getdate() as col2,getdate() as col3 
into #t 
from t t0,t t1,t t2,t t3,t t4,t t5,t t6,t t7

(100000000 row(s) affected)

This is the OP query and it takes around 12 seconds to run

SELECT col1
FROM   #t
WHERE  
    DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) 
       BETWEEN col2 
       AND     col3
;

This query which stores the date in a parameter before the execution, takes about the same time, 12 seconds.

declare @dt datetime = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) 

SELECT col1
FROM   #t
WHERE  
      @dt
       BETWEEN col2 
       AND     col3
;

And just to verify the results -
This query which does the computation on col1 and therefore have to recalculate the expression for every row takes about 30 seconds to run.

SELECT col1
FROM   #t
WHERE  
    DATEADD(dd, 0, DATEDIFF(dd, 0, col1)) 
       BETWEEN col2 
       AND     col3
;

All queries were executed repeatedly showing about the same metrics

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.