Menu
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
sp_executesql (i.e., something where the SQL Server query analyzer takes the actual parameter values into account) ORsp_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).
286
Exec vs sp_executesql The main difference between the EXEC or EXECUTE operators and the sp_executesql built-in stored procedure is that the EXEC operator is used to execute a stored procedure or a SQL command passed as a string or stored within a variable.
The use of sp_executesql is actually the only way to execute a parameterized dynamic SQL statement in SQL Server. There are two primary “execute” primitives in TDS, “batch” and “RPC”. A batch request just contains a T-SQL string, and can be used whenever there is no need for parameters.
sp_executesql is slow with parameters.
The sp_executesql is a built-in stored procedure in SQL Server that enables to execute of the dynamically constructed SQL statements or batches. Executing the dynamically constructed SQL batches is a technique used to overcome different issues in SQL programming sometimes.
Old thread I know, but I just found it by googling pretty much the exact same phrase! I had exactly the same issue (query ran very fast in Management Studio using parameters, but then really slow via ADO.Net) and replicated the issue by running the query in Management Studio via "exec sp_execute". The two execution plans were very different, even with the Optimize for query hint, so instead what I did was do an initial select of some of the data into a temporary table. That seemed to make the difference, and given you say that your query is a complex one, it might very well make the difference in your case too - I'm not quite sure how it worked, but it seemed to kick the execution plan into line even when using sp_execute.
116
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
36
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
