快SSMS和缓慢的应用 - 为什么要花这个数据集这么长，以填补？ [英] Fast in SSMS and slow in the application - Why does it take this DataSet so long to fill?
问题描述
我是从查询填充如下...一个数据集
I have a dataset which is filled from a query as follows...
SELECT DISTINCT ColA, ColB, ColC, ColD, ColE, ColF, dbo.CustomFunction(ColA) AS ColG
FROM TableA
JOIN ViewA ON ColA = ViewColA
WHERE ColB = @P1 AND ColC = @P2 AND ColD = @P3 AND ColE = @P4
ORDER BY ColB, ColC DESC, ColA
（查询字段等混淆）
(Query fields etc obfuscated)
我已经异形此查询并返回大约200行，在SSMS运行12秒。请注意，我重新启动服务器，并使用所需的DBCC命令，以确保现有的执行计划wasnt使用。
I have profiled this query and it returns around 200 rows in 12 seconds running in SSMS. Note that I restarted the server and used the required DBCC commands to ensure that an existing execution plan wasnt used.
然而，当我从我的.net应用程序运行此查询需要30秒以上，填补了数据集和超时的默认ADO.Net命令超时30秒。
However, when I run this query from my .Net application it takes over 30 seconds to fill the dataset and times out on the default ADO.Net command time out of 30 seconds.
如果查询运行在12秒内，我看不出为什么它应该不会超过18秒钟，以填补200行到一个数据集。除非有一些事情在这里，我不知道。我想，ADO.Net只是调用查询，获取数据并填充它。
If the query runs in 12 seconds, I just cannot see why it should take more than 18 more seconds to fill 200 rows into a dataset. Unless there is something going on here that I dont know about. I imagine that ADO.Net just calls the query, gets the data and populates it.
人口code看起来像这样（注意：我从另一个开发者继承了这一点）
The population code looks like this (note I have inherited this from another developer)...
DataSet res = new DataSet();
try
{
using (SqlDataAdapter da = new SqlClient.SqlDataAdapter())
{
var cmd = new SqlClient.SqlCommand();
String params = FillParameters(cmd, _params, params);
cmd.CommandText = params + SQL;
cmd.Connection = conn;
cmd.Transaction = _transaction;
if (CommandTimeout.HasValue)
{
cmd.CommandTimeout = CommandTimeout.Value;
}
da.SelectCommand = cmd;
da.Fill(res);
return res;
}
}
catch
{
throw;
}
在调试运行此，当补法被击中该方法需要约50秒才能完成。这证明通过设置较高的超时的ADO.Net命令。我很高兴与查询的性能，我可以在大约12秒内持续运行，因此，为什么更多的18+秒来填充数据集？
Running this in debug, when the fill method is hit the method takes around 50 seconds to complete. This is proved by setting a high time out on the ADO.Net command. I am happy with the performance of the query which I can run consistently in around 12 seconds so why the additional 18+ seconds to populate the dataset?
时ADO.Net做此code表示意味着它需要18秒以上来填充该数据集的东西（可能由于结构）？我曾尝试设置EnforceConstraints为false，没什么区别。
Is ADO.Net doing something (possibly due to the structure) of this code that means it takes more than 18 seconds to populate the dataset? I have tried setting EnforceConstraints to false and that makes no difference.
有一点需要注意的是，由于这个方案的设计，多参数所需要的号码输入到SQL命令。该FillParameters方法做到这一点。但是也有一些添加到命令，但只有如20左右的默认参数4所使用的这个查询。
One thing to note is that due to the design of this program, more than the required numbers of parameters are fed into sql command. The FillParameters method does this. There are 20 or so "default" parameters that are added to the command but only e.g. 4 are used by this query.
因此，在总结，
-
什么可能会发生，使其采取18+秒，填补了DS？
What could be happening to make it take 18+ seconds to fill the DS?
是ADO.Net做一些聪明用我的数据集，而不是仅仅运行查询和填充数据集？
Is ADO.Net doing something "clever" with my dataset rather than just running the query and populating the data set?
难道是在传递的参数量过多引起的问题。
Could it be the excessive amount of parameters passed in that is causing the problem.
感谢。
推荐答案
但问题是，现有的code的实施可序列化隔离级别。
The problem was that the existing code was enforcing a Serializable isolation level.
我使用SQL Server Profiler命令和执行统计数据来自查询，通过SSMS和应用的新运行比较。
I compared using SQL Server Profiler the commands and execution stats from both the query running through SSMS and the appliction.
--- SSMS ---
....
....
set transaction isolation level read committed
CPU: 7797
Reads: 338,425
Writes: 1685
Duration: 7,912
--- Application ---
....
....
set transaction isolation level serializable
CPU: 46,531
Reads: 241,202
Writes: 0
Duration: 46,792
然后我就跑使用SSMS查询两个设置事务公司版权所有级序列化和 EXEC sp_executesql的这样的SQL Server没有从SSMS的提示，以查询装的是什么。
I then ran the query in SSMS using both Set transaction isolution level serializable AND exec sp_executesql so that SQL Server had no hints from SSMS as to what the query contained.
此重放中既SSMS和应用程序的30+秒的执行时间。
This reproduced the execution time of 30+ seconds in both SSMS and the application.
这是再修改code使用提交读隔离级别的只是一个例子。
It was then just a case of modifying the code to use a Read Committed isolation level.
参考： http://www.sommarskog.se/query-plan-mysteries.html#otherreasons
这篇关于快SSMS和缓慢的应用 - 为什么要花这个数据集这么长，以填补？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
