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...