sql server - Why is a T-SQL variable comparison slower than GETDATE() function-based comparison? -

i have t-sql statement running against table many rows. seeing strange behavior. comparing datetime column against precalculated value slower comparing each row against calculation based on getdate() function.

the following sql takes 8 secs:

set transaction isolation level read uncommitted go declare @timezoneoffset int = -(datepart("hh", getutcdate() - getdate())) declare @lowertime datetime = dateadd("hh", abs(@timezoneoffset), convert(varchar, getdate(), 101) + ' 17:00:00') select top 200 id, eventdate, message  events (nolock) eventdate > @lowertime go 

this alternate strangely returns instantly:

set transaction isolation level read uncommitted go select top 200 id, eventdate, message  events (nolock) eventdate > getdate()-1 go 

why second query faster?

edited: updated sql accurately reflect other settings using

after doing lot of reading , researching, i've discovered issue here parameter sniffing. sql server attempts determine how best use indexes based on clause, in case isnt doing job.

see examples below :

slow version:

declare @dnow datetime   select @dnow=getdate()   select *   response_master_incident rmi   rmi.response_date between dateadd(hh,-2,@dnow) , @dnow   

fast version:

select *   response_master_incident rmi   rmi.response_date between dateadd(hh,-2,getdate()) , getdate()   

the "fast" version runs around 10x faster slow version. response_date field indexed , datetime type.

the solution tell sql server how best optimise query. modifying example follows include optimize option resulted in using same execution plan "fast version". optmize option here explicitly tells sql server treat local @dnow variable date (as if declaring datetime wasnt enough :s )

care should taken when doing because in more complicated clauses end making query perform worse sql server's own optimisations.

declare @dnow datetime  set @dnow=getdate()  select id, response_date, call_back_phone  response_master_incident rmi rmi.response_date between dateadd(hh,-2,@dnow) , @dnow  -- optimizer not know variable assumes should perform clusterd index scann (on clustered index id) - slow  -- hint tells optimzer variable indeed datetime in format (why not know knows) option(optimize (@dnow = '99991231'));