c# - 在 C# 与 SSMS 中使用 SqlDataReader.Read() 进行缓慢的 SQL 数据检索

Question

我正在执行一个简单的 SQL 查询来获取大量数据。查询的复杂性不是问题。执行大约需要 200 毫秒。但是，数据量似乎是问题所在。

我们检索了大约 4 万行。每行有 8 列，数据量大约为每行几百 kbytes。比如说，我们为此查询总共下载了 15megs。

让我感到困惑的是:当我从基本的 C# 代码执行查询时，它需要 1 分钟和 44 秒。但是当我从 SSMS 执行它时需要 10 秒。当然，我是在同一台机器上做的，而且我使用的是同一个数据库。我清楚地看到 UI 和实时填充的行。在 10 秒内整个数据表已满。

我们尝试过:

• 设置与 SSMS 相同的 SET 内容，
• 更改事务隔离级别，
• 忽略执行计划(使用 OPTION(RECOMPILE))，
• 忽略锁(使用 WITH(NOLOCK))。

它不会改变任何东西。有道理:读取速度慢。不是查询(恕我直言)。

需要时间的是while(reader.Read())。而且，我们尝试了一个空的 while 循环。因此，这不包括装箱/拆箱内容或将结果放入内存。

这是我们制作的一个测试程序，用于确定是 Read() 占用了时间:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;
using System.Transactions;

namespace SqlPerfTest
{
    class Program
    {
        const int GroupId = 1234;
        static readonly DateTime DateBegin = new DateTime(2017, 6, 19, 0, 0, 0, DateTimeKind.Utc);
        static readonly DateTime DateEnd = new DateTime(2017, 10, 20, 0, 0, 0, DateTimeKind.Utc);
        const string ConnectionString = "CENSORED";

        static void Main(string[] args)
        {
            TransactionOptions transactionOptions = new TransactionOptions
            {
                IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted
            };

            using (var transactionScope = new TransactionScope(TransactionScopeOption.Required, transactionOptions))
            {

                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    connection.Open();

                    SetOptimizations(connection);
                    ShowUserOptions(connection);
                    DoPhatQuery(connection).Wait(TimeSpan.FromDays(1));
                }
                transactionScope.Complete();
            }
        }

        static void SetOptimizations(SqlConnection connection)
        {
            SqlCommand cmd = connection.CreateCommand();
            Console.WriteLine("===================================");

            cmd.CommandText = "SET QUOTED_IDENTIFIER ON";
            cmd.ExecuteNonQuery();
            Console.WriteLine(cmd.CommandText);

            cmd.CommandText = "SET ANSI_NULL_DFLT_ON ON";
            cmd.ExecuteNonQuery();
            Console.WriteLine(cmd.CommandText);

            cmd.CommandText = "SET ANSI_PADDING ON";
            cmd.ExecuteNonQuery();
            Console.WriteLine(cmd.CommandText);

            cmd.CommandText = "SET ANSI_WARNINGS ON";
            cmd.ExecuteNonQuery();
            Console.WriteLine(cmd.CommandText);

            cmd.CommandText = "SET ANSI_NULLS ON";
            cmd.ExecuteNonQuery();
            Console.WriteLine(cmd.CommandText);

            cmd.CommandText = "SET CONCAT_NULL_YIELDS_NULL ON";
            cmd.ExecuteNonQuery();
            Console.WriteLine(cmd.CommandText);

            cmd.CommandText = "SET ARITHABORT ON";
            cmd.ExecuteNonQuery();
            Console.WriteLine(cmd.CommandText);

            cmd.CommandText = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED";
            cmd.ExecuteNonQuery();
            Console.WriteLine(cmd.CommandText);

            cmd.CommandText = "SET DEADLOCK_PRIORITY -1";
            cmd.ExecuteNonQuery();
            Console.WriteLine(cmd.CommandText);

            cmd.CommandText = "SET QUERY_GOVERNOR_COST_LIMIT 0";
            cmd.ExecuteNonQuery();
            Console.WriteLine(cmd.CommandText);

            cmd.CommandText = "SET TEXTSIZE 2147483647";
            cmd.ExecuteNonQuery();
            Console.WriteLine(cmd.CommandText);
        }

        static void ShowUserOptions(SqlConnection connection)
        {
            SqlCommand cmd = connection.CreateCommand();
            Console.WriteLine("===================================");

            cmd.CommandText = "DBCC USEROPTIONS";
            using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
            {
                Console.WriteLine(cmd.CommandText);

                while (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        Console.WriteLine("{0} = {1}", reader.GetString(0), reader.GetString(1));
                    }
                    reader.NextResult();
                }
            }
        }

        static async Task DoPhatQuery(SqlConnection connection)
        {
            Console.WriteLine("===================================");

            SqlCommand cmd = connection.CreateCommand();
            cmd.CommandText =
                @"SELECT
                    p.[Id],
                    p.[UserId],
                    p.[Text],
                FROM [dbo].[Post] AS p WITH (NOLOCK)
                WHERE p.[Visibility] = @visibility
                    AND p.[GroupId] = @groupId
                    AND p.[DatePosted] >= @dateBegin
                    AND p.[DatePosted] < @dateEnd
                ORDER BY p.[DatePosted] DESC
                OPTION(RECOMPILE)";
            cmd.Parameters.Add("@visibility", SqlDbType.Int).Value = 0;
            cmd.Parameters.Add("@groupId", SqlDbType.Int).Value = GroupId;
            cmd.Parameters.Add("@dateBegin", SqlDbType.DateTime).Value = DateBegin;
            cmd.Parameters.Add("@dateEnd", SqlDbType.DateTime).Value = DateEnd;
            Console.WriteLine(cmd.CommandText);
            Console.WriteLine("===================================");

            DateTime beforeCommit = DateTime.UtcNow;
            using (SqlDataReader reader = await cmd.ExecuteReaderAsync(CommandBehavior.CloseConnection))
            {
                DateTime afterCommit = DateTime.UtcNow;
                Console.WriteLine("Query time = {0}", afterCommit - beforeCommit);

                DateTime beforeRead = DateTime.UtcNow;
                int currentRow = 0;
                while (reader.HasRows)
                {
                    while (await reader.ReadAsync())
                    {
                        if (currentRow++ % 1000 == 0)
                            Console.WriteLine("[{0}] Rows read = {1}", DateTime.UtcNow, currentRow);
                    }
                    await reader.NextResultAsync();
                }

                Console.WriteLine("[{0}] Rows read = {1}", DateTime.UtcNow, currentRow);

                DateTime afterRead = DateTime.UtcNow;
                Console.WriteLine("Read time = {0}", afterRead - beforeRead);
            }
        }
    }
}

正如您在上面看到的，我们重现了与 SSMS 中相同的 SET 内容。我们还尝试了所有人类已知的技巧来加速一切。使用异步的东西。使用 WITH(NOLOCK)、NO RECOMPILE、在连接字符串中定义更大的 PacketSize 并没有帮助，并且使用 Sequential Reader。尽管如此，SSMS 还是快了 50 倍。

更多信息

我们的数据库是 Azure 数据库。我们实际上有 2 个数据库，一个在欧洲，一个在美国西部。由于我们位于欧洲，所以当我们使用欧洲数据库时，同样的查询速度更快。但它仍然像 30 秒，就像 SSMS 中的即时一样。数据传输速度确实会影响这一点，但这不是主要问题。

我们还可以通过投影较少的列来减少数据传输的时间。当然，它确实加快了 Read() 迭代。假设我们只检索我们的 ID 列:那么我们有一个持续 5 秒的 while(Read())。但这不是一个选项，因为我们需要所有这些列。

我们知道如何“解决”这个问题:我们可以以不同的方式处理我们的问题，每天进行小查询并将这些结果缓存在 Azure 表或其他东西中。但是我们想知道为什么 SSMS 更快。有什么诀窍。

我们在 C# 中使用 Entity Framework，在 C# 中使用 Dapper，上面的示例就像原生 C#。我在 interwebz 中看到一些人可能有类似的问题。对我来说，感觉是 SqlDataReader 比较慢。比如，它不会使用多个连接或其他方式对行的下载进行管道传输。

问题

所以我的问题是:Management Studio 到底是如何设法将查询结果的下载速度提高 50 倍的？有什么诀窍？

谢谢你们。

Answer 1

What boggles my mind is that: when I execute the query from a basic C# code it takes 1min and 44secs. But when I do it from SSMS it takes 10 secs

您不能直接在 SSMS 中执行参数化查询，因此您要比较不同的东西。当您在 SSMS 中使用局部变量而不是参数时，SQL Server 会使用总体平均密度统计信息来估计行数。对于参数化查询，SQL Server 使用统计直方图和提供的参数值进行初始编译。不同的估计会导致不同的计划，尽管直方图的估计通常更准确并且产生更好的计划(理论上)。

尝试更新统计信息并使用 sp_executesql 和参数从 SSMS 执行查询。我期望与应用程序代码具有相同的性能，无论好坏。