从 ADO.NET 与 SMSS 相比，具有相同查询计划的相同查询需要大约 10 倍的时间 [英] Same query with the same query plan takes ~10x longer when executed from ADO.NET vs. SMSS

问题描述

我的查询相当复杂，但我已对其进行了简化以解决此问题，现在它是一个简单的 JOIN，我在 SQL Server 2014 数据库上运行.查询是:

My query is fairly complex, but I have simplified it to figure out this problem and now it is a simple JOIN that I'm running on a SQL Server 2014 database. The query is:

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

当我从 SMSS 运行此查询并在 SQL Profiler 中观察它时，执行大约需要 350 毫秒.当我使用 Entity Framework 或 ADO.NET 在我的应用程序中运行相同的查询时(我都尝试过).执行需要 4500 毫秒.

When I run this query from SMSS and watch it in SQL Profiler, it takes around 350ms to execute. When I run the same query inside my application using Entity Framework or ADO.NET (I've tried both). It takes 4500ms to execute.

ADO.NET 代码:

ADO.NET Code:

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()
}

我进行了广泛的 Google 搜索，发现 这篇很棒的文章和几个 StackOverflow 问题(这里和这里).为了使两个查询的会话参数相同，我在 ADO.NET 中调用 SET ARITHABORT ON 并没有区别.这是一个直接的 SQL 查询，所以不存在参数嗅探问题.我已将查询和索引简化为该测试的最基本形式.服务器上没有其他任何东西在运行，并且在测试期间没有其他任何东西访问数据库.Cars 或 SportsCars 表中没有计算列，只有 INT 和 VARCHAR.

I've done an extensive Google search and found this awesome article and several StackOverflow questions (here and here). In order to make the session parameters identical for both queries I call SET ARITHABORT ON in ADO.NET and it makes no difference. This is a straight SQL query, so there is not a parameter sniffing problem. I've simplified the query and the indexes down to their most basic form for this test. There is nothing else running on the server and there is nothing else accessing the database during the test. There are no computed columns in the Cars or SportsCars table, just INTs and VARCHARs.

SportsCars 表有大约 170k 条记录和 4 列，Cars 表有大约 120 万条记录和 7 列.结果数据集(SportsCars of Type=1)有大约 2600 条记录和 11 列.我在 Cars 表的 [Type] 列上有一个非聚集索引，该列包括汽车表的所有列.并且这两个表在 CarID 列上都有一个聚集索引.两个表上都不存在其他索引.在这两种情况下，我都以相同的数据库用户身份运行.

The SportsCars table has about 170k records and 4 columns, and the Cars table has about 1.2M records and 7 columns. The resulting data set (SportsCars of Type=1) has about 2600 records and 11 columns. I have a single non-clustered index on the Cars table, on the [Type] column that includes all the columns of the cars table. And both tables have a clustered index on the CarID column. No other indexes exist on either table. I'm running as the same database user in both cases.

当我在 SQL Profiler 中查看数据时，我发现两个查询都使用完全相同的非常简单的查询计划.在 SQL Profiler 中，我使用了 Performance Event Class 和 ShowPlan XML Statistics Profile，我认为这是监视和捕获实际执行计划的适当事件.两个查询的读取次数相同 (2596).

When I view the data in SQL Profiler, I see that both queries are using the exact same, very simple query plan. In SQL Profiler, I'm using the Performance Event Class and the ShowPlan XML Statistics Profile, which I believe to be the proper event to monitor and capture the actual execution plan. The # of reads is the same for both queries (2596).

在 ADO.NET 与 SMSS 中，使用完全相同的查询计划的两个完全相同的查询如何花费 10 倍的时间?

How can two exact same queries with the exact same query plan take 10x longer in ADO.NET vs. SMSS?

推荐答案

想通了:

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

Because I'm using Entity Framework, the connection string in my application has MultipleActiveResultSets=True. When I remove this from the connection string, the queries have the same performance in ADO.NET and SSMS.

显然，此设置存在问题，导致查询在通过 WAN 连接到 SQL Server 时响应缓慢.我发现 这个链接和这个评论:

Apparently there is an issue with this setting causing queries to respond slowly when connected to SQL Server via WAN. I found this link and this comment:

MARS 使用firehose 模式"来检索数据.Firehose 模式意味着服务器将尽可能快地生成数据.这也意味着您的客户端应用程序必须以相同的速度接收入站数据它进来了.如果没有，服务器上的数据存储缓冲区将填满，处理将停止，直到这些缓冲区清空.

MARS uses "firehose mode" to retrieve data. Firehose mode means that the server will produce data as fast as possible. This also means that your client application must receive inbound data at the same speed as it comes in. If it doesn't the data storage buffers on the server will fill up and the processing will stop until those buffers empty.

那又怎样?您可能会问...但是只要停止处理SQL 服务器上的资源正在使用中并且被占用.这包括工作线程、模式和数据锁、内存等.所以它是您的客户端应用程序将入站结果作为他们到达时很快.

So what? You may ask... But as long as the processing is stopped the resources on the SQL server are in use and are tied up. This includes the worker thread, schema and data locks, memory, etc. So it is crucial that your client application consumes the inbound results as quickly as they arrive.

我必须将此设置与实体框架一起使用，否则延迟加载会产生异常.所以我将不得不想出一些其他的解决方法.但至少我现在明白了这个问题.

I have to use this setting with Entity Framework otherwise lazy loading will generate exceptions. So I'm going to have to figure out some other workaround. But at least I understand the issue now.

这篇关于从 ADO.NET 与 SMSS 相比，具有相同查询计划的相同查询需要大约 10 倍的时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！