SSMS速度快而应用程序速度慢-为什么要花费这么长时间才能填充此DataSet？

Question

我有一个从查询中填充的数据集，如下所示…

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

（混淆查询字段）

我已经剖析了此查询，它在12秒钟内在SSMS中运行时返回了大约200行。请注意，我重新启动了服务器，并使用了必需的DBCC命令来确保未使用现有的执行计划。

但是，当我从.Net应用程序运行此查询时，将花费30秒以上的时间来填充数据集，并且默认的ADO.Net命令超时会超过30秒。

如果查询在12秒内运行，我只是看不到为什么要花200多行才能将200行填充到数据集中的原因。除非这里发生了我不知道的事情。我想象ADO.Net只是调用查询，获取数据并填充它。

人口代码看起来像这样（请注意，我是从另一个开发人员那里继承过来的）…

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;
    }

在调试中运行该命令，当点击fill方法时，该方法大约需要50秒钟才能完成。通过在ADO.Net命令上设置较高的超时时间可以证明这一点。我对查询的性能感到满意，该查询可以在12秒左右的时间内持续运行，那么为什么还要花18多秒才能填充数据集呢？

ADO.Net是否正在执行此代码（可能由于结构）而导致填充数据集需要18秒钟以上的时间？我尝试将EnforceConstraints设置为false，这没有什么区别。

需要注意的一件事是，由于该程序的设计，将超过所需数量的参数输入到sql命令中。FillParameters方法执行此操作。该命令中添加了大约20个“默认”参数，但此查询仅使用了例如4个。

总而言之，

• 怎么可能需要18秒钟以上才能填充DS？

• ADO.Net是否对我的数据集做一些“聪明”的事情，而不是仅仅运行查询并填充数据集？

• 可能是传入的参数过多导致了问题。

谢谢。

Answer 1

问题在于现有代码正在强制执行可序列化隔离级别。

我使用SQL Server Profiler比较了通过SSMS运行的查询和应用程序的命令和执行状态。

--- 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 serializableAND在SSMS中运行查询，exec sp_executesql以便SQL Server无法从SSMS得知查询包含的内容。

这在SSMS和应用程序中均重现了30+秒的执行时间。

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

参考：http :
//www.sommarskog.se/query-plan-
mysteries.html#otherreasons