Index not used eficiently when used with variable

1

2 queries..look same..but runs slow when I use variable in where clause:(

index on transmissionDTM column

use database
go
--runs fast - 2 secs
select col1, col2,transmissionDTM from table1
where transmissionDTM between '5/16/2016 07:00' and '5/16/2016 07:30'
order by 3 desc
go

--runs slow...30-40 secs

declare @startdate datetime = '5/16/2016 07:00'
declare @enddate datetime
set @enddate = DATEADD(mi, 30, @startdate)

select col1, col2,transmissionDTM from table1
where transmissionDTM between @startdate and @enddate

order by 3 desc

2

Try forcing SQL to re-evaluate the variables when creating the access plan:

select col1, col2,transmissionDTM from table1
where transmissionDTM between @startdate and @enddate
order by 3 desc
option recompile