Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Bhoomi
Starting Member
1 Post |
 Posted - 2008-06-04 : 00:25:20
|
| 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. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-06-04 : 01:23:59
|
Simple. The SQL Server the number of IO needed tofetch all your records.If another index seems to be a better candidate, then SQL Server will use that index instead.Many other factors are weighted, such as sequential IO reads are preferred over random IO reads.Keep that in mind and reorganize/rebuild/defrag your indexes!
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
|
|
|
|
|
DateTime DataType not using index for variables.