SSMS 快而应用程序慢 - 为什么这个 DataSet 需要这么长时间才能填充? [英] 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

(查询字段等被混淆)

我已经分析了这个查询，它在 SSMS 中运行 12 秒后返回了大约 200 行.请注意，我重新启动了服务器并使用了所需的 DBCC 命令来确保没有使用现有的执行计划.

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 秒内运行，我只是不明白为什么要将 200 行填充到数据集中需要超过 18 秒.除非这里发生了我不知道的事情.我想 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.

人口代码看起来像这样(注意我从另一个开发人员那里继承了这个)...

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 是否在执行此代码的某些操作(可能是由于结构原因)，这意味着填充数据集需要超过 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.

总之，

• 导致填写 DS 需要 18 多秒的时间是什么原因?

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

谢谢.

推荐答案

问题是现有代码强制执行 Serializable 隔离级别.

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

然后，我使用 Set transaction isolution level serializable 和 exec sp_executesql 在 SSMS 中运行查询，这样 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.

这只是修改代码以使用 Read Committed 隔离级别的情况.

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 快而应用程序慢 - 为什么这个 DataSet 需要这么长时间才能填充?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！