Why is date picking slow
I have a table with approximately 1.5 million rows where date_run is indexed without a cluster. Request # 1 takes 0 seconds and Request # 2 takes 3 seconds. Can someone explain why query # 2 is slower. I've also included execution plans for both. Sql Server 2014.
request # 1
select avg14gain
from stocktrack
where
date_run >= '2013-3-21'
and date_run < '2013-3-22'
Valid XHTML http://biginkz.com/Pics/DateHardCoded.jpg .
request # 2
declare @today date
declare @yesterday date
set @today='2013-3-22'
set @yesterday='2013-3-21'
select avg14gain
from stocktrack
where
date_run >= @yesterday
and b.date_run <@today
+3
source to share
2 answers
I'm not sure why your query is not picking up the index, but you can use an index hint .
Try something like this:
declare @today date
declare @yesterday date
set @today='2013-3-22'
set @yesterday='2013-3-21'
select avg14gain
from stocktrack
where
date_run >= @yesterday
and b.date_run <@today
with (index([stocktrack].[ix_drun]))
also you can try what is suggested in this post: TSQL does not use indexes . See @Justin Dearing's answer (rebuild index / update statistics).
0
source to share