Prevent ADO.NET from using sp_execute

Question

In our SQL Server 2005 database (tested using Management Studio with DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS), the following statement is fast (~0.2s compile time, ~0.1s execution time):

SELECT ... FROM ... WHERE a = 1 AND b = '' ...

The following statement, however, is slow (~0.2s compile time, 7-11s execution time):

exec sp_executesql N'SELECT ... FROM ... WHERE a = @a AND b = @b ...', N'@a int, @b nvarchar(4000), ...', @a=1, @b=N'', ...

SQL Server chooses a different execution plan, although the queries are equal. This makes sense, since, in the first case, SQL Server has the actual values of a, b and all the other parameters available and can use the statistics to create a better plan. Apparently, the query plan for the concrete values of the parameters is much better than the generic one and definitely outweighs any "query plan caching" performance benefit.

Now my question: ADO.NET always seems to use the second option (sp_executesql) when executing parameterized queries, which usually makes sense (query plan caching, etc.). In our case, however, this kills performance. So, is there some way to either

• force ADO.NET to use something different than sp_executesql (i.e., something where the SQL Server query analyzer takes the actual parameter values into account) OR
• force SQL Server to recaclulate the query plan of the SQL passed to sp_executesql taking the parameter values into account?

And please don't tell me I have to go back to ugly, old, dangerous sql = "WHERE b = " + quoteAndEscape(parameterB)...

Putting the SQL into a stored procedure makes no difference (slow, with and without WITH RECOMPILE). I did not post the actual SQL statment since it is quite complex (joins over multiple tables, including sub-SELECTs and aggregation).

Answer 1

You could try the OPTIMIZE FOR query hint which (quote):

Instructs the query optimizer to use a particular value for a local variable when the query is compiled and optimized. The value is used only during query optimization, and not during query execution. OPTIMIZE FOR can counteract the parameter detection behavior of the optimizer or can be used when you create plan guides