Index Scanning

Please read following statements. statement 1 runs very faster and uses index scan. The statement 2 runs very slow uses table scan.

The Following things are already done.

DBCC DBREINDEX
Sp_updatestats
update statistics

It is only affecting one column (date_added) and also other statistic name start with _WA_SYS_* but date column starts as statistic_date_added. This has happened only for the last two days. All the production store procedure accessing date_columns now running longer.

Is there anybod can explain this and please post a solution. ?

Raj Sankar

Statement 1.
select * from rx_control where store_id = @.store_id and date_added between '08/01/02' and '08/20/02'
------------------------
statement 2.
------------
declare @.bdate as datetime
declare @.edate as datetime
declare @.rxid as int

set @.store_id = '52'
set @.rxid = '158315'
set @.bdate = '08/01/02'
set @.edate = '08/10/02'

select * from rx_control where store_id = @.store_id and date_added between @.bdate and @.edate
------------------------check with dbcc showcontig report and depending on this u should go for dbcc indexfrag on respective fields.

if still the problem persists send me the showcontig report for the respectve table if u can.|||I had problem like this or very similar.
Query optimizer creates an execution plan based on conditions. For the first statement optimizer knows date range exactly and creates the best execution plan.
The second statement is using local variables and optimizer creates common execution plan (without using particular conditions).
How to resolve this problem? In my situation I created additional table where parameters were saved and I was using join. It looks like a stupid decision but it works.
You can try to use this idea.

Good luck