防止ADO.NET使用sp_executesql [英] Prevent ADO.NET from using sp_executesql

问题描述

在我们的SQL Server 2005数据库中（使用Management Studio在 DBCC FREEPROCCACHE 和
DBCC DROPCLEANBUFFERS 中进行了测试），以下语句很快（约0.2s的编译时间，约0.1s的执行时间）：

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

但是，以下语句，很慢（〜0.2s的编译时间， 7-11s 执行时间）：

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选择不同的执行计划，尽管查询是相同的。这是有道理的，因为在第一种情况下，SQL Server的实际值为 a ， b ，并且所有其他可用参数，并可以使用统计信息来制定更好的计划。显然，参数的具体值的查询计划比通用的查询计划要好得多，并且肯定比任何查询计划缓存性能收益都重要。

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.

现在我的问题是：在执行参数化查询时，ADO.NET似乎总是使用第二个选项（sp_executesql），这通常是合理的（查询计划缓存等）。但是，在我们的情况下，这会降低性能。因此，是否可以通过某种方式

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

• 强制ADO.NET使用不同于 sp_executesql （即SQL Server查询分析器考虑实际参数值的地方）或


• 强制SQL Server重新计算传递给<$ c $的SQL的查询计划c> sp_executesql 考虑参数值？

• 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?

然后请不要告诉我，我必须回到丑陋，古老，危险的 sql = WHERE b = + quoteAndEscape（parameterB） ...

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

将SQL放入存储过程中没有什么区别（慢，有和没有，并且需要重新编译）。我没有发布实际的SQL语句，因为它非常复杂（连接多个表，包括子SELECT和聚合）。

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

推荐答案

我知道旧线程，但是我只是通过搜索几乎完全相同的短语而发现的！我遇到了完全相同的问题（使用参数在Management Studio中查询运行非常快，但通过ADO.Net却非常慢），并通过 exec sp_execute在Management Studio中运行查询来复制该问题。即使使用优化查询提示，这两个执行计划也有很大不同，所以我所做的是将一些数据初始选择到临时表中。这似乎有所不同，考虑到您所说的查询是一个复杂的查询，它也很可能对您的情况有所不同-我不太确定它是如何工作的，但似乎可以将执行计划纳入其中即使使用sp_execute也是如此。

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.

这篇关于防止ADO.NET使用sp_executesql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！