What is the best index or ideas to improve date range lookups? (or query change)
begin tran t1
delete from h1 where histdate between @d1 and @d2 and HistRecordType = 'L'
insert into h1
select * from v_l WHERE HistDate between @d1 and @d2
commit tran t1
it is far slower than histdate = @d1
I have a clustered, non-unique index on the date column
however, the perf is the same switching to a non-clustered
if @d1 = @d2.. the query takes 8mins to run, histdate=@d1 runs in 1 second (so that should sort of be equiv right?)