首页 文章

从ADO.NET和SMSS执行时，具有相同查询计划的相同查询需要大约10倍的时间

提问于
浏览 次
6

我的查询相当复杂，但我已经简化了它来解决这个问题，现在它是一个简单的JOIN，我在SQL Server 2014数据库上运行 . 查询是：

SELECT * FROM SportsCars as sc INNER JOIN Cars AS c ON c.CarID = sc.CarID WHERE c.Type = 1

当我从SMSS运行此查询并在SQL事件探查器中观察它时，执行大约需要350毫秒 . 当我使用Entity Framework或ADO.NET在我的应用程序中运行相同的查询时（我已尝试过两者） . 执行需要4500ms .

ADO.NET代码：

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    var cmdA = new SqlCommand("SET ARITHABORT ON", connection);
    cmdA.ExecuteNonQuery();

    var query = "SELECT * FROM SportsCars as sc INNER JOIN Cars AS c ON c.CarID = sc.CarID WHERE c.Type = 1";
    var cmd = new SqlCommand(query, connection);
    cmd.ExecuteNonQuery()
}

我做了大量的谷歌搜索，找到了this awesome article和几个StackOverflow问题（here和here） . 为了使两个查询的会话参数相同，我在ADO.NET中调用 SET ARITHABORT ON 并没有什么区别 . 这是一个直接的SQL查询，因此没有参数嗅探问题 . 我已将查询和索引简化为此测试的最基本形式 . 服务器上没有其他任何东西运行，在测试期间没有其他任何东西访问数据库 . Cars或SportsCars表中没有计算列，只有INT和VARCHAR .

SportsCars表有大约170k记录和4列，Cars表有大约1.2M记录和7列 . 结果数据集（SportsCars of Type = 1）有大约2600条记录和11列 . 我在Cars表上有一个非聚集索引，在[Type]列上包含cars表的所有列 . 两个表都在CarID列上有一个聚簇索引 . 两个表上都不存在其他索引 . 在这两种情况下，我都以相同的数据库用户身份运行 .

当我在SQL事件探查器中查看数据时，我发现两个查询都使用完全相同，非常简单的查询计划 . 在SQL事件探查器中，我使用了性能事件类和ShowPlan XML统计信息配置文件，我认为这是监视和捕获实际执行计划的正确事件 . 两个查询的读取数相同（2596） .

在ADO.NET与SMSS中，具有完全相同查询计划的两个完全相同的查询如何才能延长10倍？

c# sql-server performance tsql ado.net

2 回答

  • 4

    弄清楚了：

    因为我正在使用Entity Framework，所以我的应用程序中的连接字符串有 MultipleActiveResultSets=True . 当我从连接字符串中删除它时，查询在ADO.NET和SSMS中具有相同的性能 .

    显然，此设置存在问题，导致查询在通过WAN连接到SQL Server时响应缓慢 . 我发现this link和comment：

    MARS使用“firehose模式”来检索数据 . Firehose模式意味着服务器将尽快生成数据 . 这也意味着您的客户端应用程序必须以与其相同的速度接收入站数据 . 如果不是，则服务器上的数据存储缓冲区将填满，处理将停止，直到这些缓冲区为空 . 所以呢？您可能会问...但是只要处理停止，SQL服务器上的资源就会被使用并且被捆绑起来 . 这包括工作线程，模式和数据锁，内存等 . 因此，客户端应用程序在到达时尽快消耗入站结果至关重要 .

    我必须在Entity Framework中使用此设置，否则延迟加载将生成异常 . 所以我将不得不弄清楚其他一些解决方法 . 但至少我现在理解这个问题 .

    回复于
  • 0

    在ADO.NET与SMSS中，具有完全相同查询计划的两个完全相同的查询如何才能延长10倍？

    首先，我们需要明确有关查询和查询计划的"same" . 假设问题最顶端的查询是复制粘贴，那么它与通过ADO.NET提交的查询不同 . 要使两个查询相同，它们需要逐字节相同，包括所有空格，大写，标点符号，注释等 .

    显示的两个查询肯定非常相似 . 他们甚至可能共享相同的执行计划 . 但那些人如何确定“相同”呢？这两种情况下的XML是否相同？或者只是在查看计划时在SSMS中以图形方式显示的内容？如果根据图形表示确定它们是相同的，那么这有时会产生误导 . 需要检查XML本身 . 即使两个查询计划具有相同的查询哈希，仍然（有时）部分查询计划是可变的，并且更改不会更改计划哈希 . 一个例子是表达式的评估 . 有时它们被计算并且它们的结果作为常数嵌入到计划中 . 有时它们是在每次执行开始时计算的，并在该特定执行中存储和重用，但不适用于任何后续执行 .

    SSMS和ADO.NET之间的一个区别是每个的默认会话属性 . 我以为我在几年前看过一个图表显示了ADO / OLEDB / SQLNCLI的默认值，但可以__17688_需要猜测工作，因为它可以使用SESSIONPROPERTY函数发现 . 只需在C＃代码中运行此查询，而不是当前的SELECT，和在调试中检查结果或打印出来或其他什么 . 无论哪种方式，运行这样的事情：

    SELECT SESSIONPROPERTY('ANSI_NULLS') AS [AnsiNulls],
       SESSIONPROPERTY('ANSI_PADDING') AS [AnsiPadding],
       SESSIONPROPERTY('CONCAT_NULL_YIELDS_NULL') AS [ConcatNullYieldsNull],
       ...;

    确保获取链接的MSDN页面中记录的所有设置 . 现在，在SSMS中，转到“查询”菜单，选择“查询选项...”，然后转到“执行”| “ANSI” . 从C＃代码返回的设置需要与SSMS中显示的设置相匹配 . 任何设置不同的东西都需要在ADO.NET查询字符串的开头添加这样的东西：

    SET ANSI_NULLS ON;
{rest of query}

    现在，如果您想要消除DataTable加载可能的嫌疑，只需替换该行，只需替换：

    var cars = new DataTable();
cars.Load(reader);

    有：

    while(reader.Read());

    最后，为什么不把查询放入存储过程？通常最重要的会话设置（即ANSI_NULLS等）与proc定义一起存储，因此无论您是从SSMS执行EXEC还是从ADO.NET执行，它们都应该工作相同（同样，我们不在此处理任何参数） .

    回复于

相关问题