从 ADO.NET 与 SMSS 相比，具有相同查询计划的相同查询需要大约 10 倍的时间-C#/.NET问题

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

本文介绍了从 ADO.NET 与 SMSS 相比，具有相同查询计划的相同查询需要大约 10 倍的时间的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我的查询相当复杂，但我已对其进行了简化以解决此问题，现在它是一个简单的 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?