June 3, 2008 at 10:22 pm
Hi,
I have a strange problem in sqlserver 2000.
Index on DateTime is not applied when variable is used in "between...and" clause whereas it is applied when fixed value used in "between..and" clause of DateTime datatype.
Index is present in j.dtTransaction Column.
1. Query where Variable is used in between...and clause.
declare @filter1 int, @filter2 int, @status int
declare @dtFromDate datetime, @dtToDate datetime
set @filter1 = 10000000
set @filter2 = 1000000
set @status =1
set @dtFromDate = '2008/05/14'
set @dtToDate = '2008/06/03'
select * from btjournal j
inner join bmCompany co on scompanyid = j.scompanyidfk
where ((j.dtTransaction between @dtFromDate and @dtToDate )
and (j.nTagStatus % @filter1 / @filter2 = @status))
or (@dtFrom is null and 1=2)
Query very slow with 5 lakh record in btjournal, other table bmcompany has 50 records.
Execution plan shows that 99% was spent on a clustered index and index on dtTransaction was not used for scanning.
2. Query where hard code value is used in between....and clause.
declare @filter1 int, @filter2 int, @status int,@dtFrom char(10)
set @filter1 = 10000000
set @filter2 = 1000000
set @status =1
select *
from btjournal j
inner join bmCompany co on scompanyid = j.scompanyidfk
where ((j.dtTransaction between '2008/05/14' and '2008/06/04')
and (nTagStatus % @filter1 / @filter2 = @status))
or (@dtFrom is null and 1=2)
Query very quick with 5 lakh record in btjournal, other table bmcompany has 50 records.
Execution plan shows that index on dtTransaction was used for scanning.
My confusion is why sqlserver 2000 does not use index on DateTime when a variable is used in between...and clause.
Thanks in advance,
Regards,
Bhoomi.
June 3, 2008 at 10:36 pm
Hi,
Sometimes it won't include the dates specified in between clause. So better use like this, j.dtTransaction >= @FromDate and j.dtTransaction < @ToDate
Please give us some sample input data. Let me know, if u have still have any doubts.
June 3, 2008 at 11:27 pm
Hi
I think the issue might be related to parameterization. When u use variables, those variables can have many different values each time you execute the query. So SQL makes the query plan accordingly and it seems to think it best not to use the index on the date column
On the other hand when you hard code values SQL knows that the same value is going to be used each time the query will be executed and makes a query plan accordingly. It decides that the best way is use the index on the date column.
Hope i have pointed out correctly...
"Keep Trying"
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply