Index Help

[PavanKumarU]

Hello,
Have been struggling with the following query for a couple of days and hope someone could shed some light on this:
The query:
Select id, tran_no
from table_x
where
client=@client
and price=(case when @variable1=0 then @variable2 else @variable1 end)
and date=(case when @datevariable1='' then @datevariable2 else @datevariable1 end)

The background:
There are three indexes for this table.
1)First index - ID (clustered)
2)Second - Tran_no (NC)
3)Client, price, date (Non clustered)
There is no primary key.

The problem:
When run in a databse with 'Set showplan on' the query uses index #3 and returns results fast. When run in another databse it doesnt use any index and uses table scan. We are now forcing the index with this query and it runs fine. Couldnt we run it without forcing the index and still get quick results? I assume (and beleive) there is nothing worng on our side. It must be something on the server side that causing this problem. Could someone throw some light on why this happens and what should be done?

Also, when i hard code a zero for 2client it doesnt use the index. When i give any other number it uses the query.
Any help is much appreciated.

Thanks
Pavan

 

[gpadhy]

run update statistics on the table and run the query again

 

[pathakpr]

I strongly believe that the table statistics is not up to date. Update the statistics of a table (in a database where it is not using index) nd then try rerunning.