quote:

---

Originally posted by man_tdh2002

Hi
when i execute a hardcoded query it performs index seek where as incase of dynamic execution it performs index scan. how can i change the set up/data access path so that it should also perform index seek during dynamic execution

hi
given below are my static and dynamic querries.
I am using preparedstatement from java to update the rows.everytime i'll be updating only one row based on unique index

unique index is based on bank_id,branch_id,acid,bal_type


static
======
update acbl set bank_id='024',branch_id='000100' ,acid='AFO01D', bal_amt =.000 where bank_id='024'and branch_id='000100' and acid='AFO01D' and bal_type='670'


dynamic(from java application through preparedstatement)
===============================
exec sp_executesql N' UPDATE ACBL SET Bal_Amt=@P1,Bal_Desc=@P2,Bal_Expl=@P3,Del_Flg=@P4,R_Mod_Id=@P5,R_Mod_Time=@P6 WHERE Bank_Id = @P7 AND Branch_Id = @P8 AND Acid = @P9 AND Bal_Type = @P10', N'@P1 float ,@P2 nvarchar(4000) ,@P3 nvarchar(4000) ,@P4 nvarchar(4000) ,@P5 nvarchar(4000) ,@P6 datetime ,@P7 nvarchar(4000) ,@P8 nvarchar(4000) ,@P9 nvarchar(4000) ,@P10 nvarchar(4000) ', 5.000000000000000e+000, N'from abcd', N'from corppwdprint', N'Y', N'ABCD', 'Nov 7 2004 5:44:02:880PM', N'024', N'000100', N'AFO01D', N'670'

hope this clarifies your doubt
my query will at max update only one row ,since the where clause is based on unique index .so the query optimizer should use the index seek ,instead it's using index scan and taking some 150 ms.

---