bad performance with parameterized sql statements | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

bad performance with parameterized sql statements

I have a problem with large tables (> 5 million records) and the parameterized version of an sql statement passed to the sp_executesql procedure. A ‘normale’ statement without the parameterized syntax just takes mills, because the criteria is the primary key and is well indexed. The parameterized alternative takes over 6 seconds.
First of all, i thought respective read that the parameterized version for the sp_executesql would be better and faster because the query optimizer will reuse execution plans.
I cannot use the ‘simple’ statement, because the statements are not generated by mine.
Has anyone a clue, why this is so slow. Here is an example. First takes 6 sec exec sp_executesql N’UPDATE History SET [email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected] WHERE hisOID = @P12 ‘, N’@P1 datetime ,@P2 nvarchar(4000) ,@P3 datetime ,@P4 nvarchar(4000) ,@P5 nvarchar(4000) ,@P6 nvarchar(4000) ,@P7 datetime ,@P8 nvarchar(4000) ,@P9 nvarchar(4000) ,@P10 nvarchar(4000) ,@P11 nvarchar(4000) ,@P12 nvarchar(4000) ‘, ‘Jul 28 2003 5:17:32:000PM’, N’ibrahimk’, ‘Okt 20 2003 5:29:29:777PM’, N’SYSTEM’, N’SV: xxx’, N”, ‘Jul 28 2003 5:17:31:000PM’, NULL, N’90’, N’1080′, N’5CCFCCE8-B144-11D7-A860-0007E90F2D57′, N’FC7C1593-9D39-4D21-9E02-B86F91DCCDD7′
Second takes just mills exec sp_executesql N’UPDATE History SET hisLockTimestamp=”Jul 28 2003 5:17:31:000PM”,hisLockUser=”ibrahimk”,hisLastModified=”Jul 28 2003 5:17:31:000PM”,hisLastModifier=”SYSTEM”,hisShortDescription=”SV: xxx”,hisDescription=””,hisCreation=”Jul 28 2003 5:17:31:000PM”,hisAttachmentOID=””,hisOrderStatusOID=”90”,hisHistoryTypeOID=”1080”,hisOrderOID=”5CCFCCE8-B144-11D7-A860-0007E90F2D57” WHERE hisOID = ”FC7C1593-9D39-4D21-9E02-B86F91DCCDD7”’
with kind regards
isnogud
If the object names in the string are not fully qualified, then the execution plan will not be reused. Try using the fully qualified name for the History table.
i think with fully qualified you mean up to db server (dbserver.db.dbo.history). i tried this, but it had no effect. the different duration is still as high as before.
Did you try execution plan for both cases? Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
May check this linkhttp://www.algonet.se/~sommar/dynamic_sql.html (contributed by FrankKalis in a post) for tips. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
i analysed both execution plans. i examine that the dynamic sql statement doesn’t use the primary key index.
why does the parameterized vision doesnot realize that the query depends on the pk.
is it possible to define own execution plans or influence the execution plan.
Is the datatype of hisOID really nvarchar(4000)? The parameterised query says it is… Ensure that the parameterised query uses the correct datatype for the column that it is representing If it is really a varchar rather than nvarchar then that would explain it not using an index… Cheers
Twan
]]>