sql server - Prevent ADO.NET from using sp_executesql -
in our sql server 2005 database (tested using management studio dbcc freeproccache , dbcc dropcleanbuffers ), following statement fast (~0.2s compile time, ~0.1s execution time): select ... ... = 1 , b = '' ... the following statement, however, slow (~0.2s compile time, 7-11s execution time): exec sp_executesql n'select ... ... = @a , b = @b ...', n'@a int, @b nvarchar(4000), ...', @a=1, @b=n'', ... sql server chooses different execution plan, although queries equal. makes sense, since, in first case, sql server has actual values of a , b , other parameters available , can use statistics create better plan. apparently, query plan concrete values of parameters much better generic 1 , outweighs "query plan caching" performance benefit. now question: ado.net seems use second option (sp_executesql) when executing parameterized queries, usually makes sense (query plan caching, etc.). in our case, however, kills performance. so, t...