在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秒内运行，我就不明白为什么需要超过18秒才能将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？
• 正在用我的数据集做一些“聪明”的事情，而不仅仅是运行查询和填充数据集吗？
• 是否是通过过多的参数导致问题的原因呢？

谢谢。

Answer 1

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

我比较了使用、通过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 serializable和exec sp_executesql在SSMS中运行查询，这样Server就不会从SSMS那里得到查询包含什么的提示。

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

那时只是修改代码以使用Read Committed隔离级别的情况。

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

Answer 2

假设您的表有主键

1)如果碎片> 30%，检查现有索引碎片，否则重新构建索引，2)检查缺少的索引列是否创建基于缺失列的非集群索引。

然后重新运行sql脚本。一般情况下，在管理好适当的指标后应有所改进。

Answer 3

我认为您的问题不在代码中，而是在数据库中，如果您有一个大型数据库，那么这可能是您的问题，请尝试使用以下方法更新数据库中的统计数据：

EXEC sp_updatestats