时间戳

首页 > 解决方案 > 异步 SqlClient 方法的好处

c# - 异步 SqlClient 方法的好处

问题描述

*Async命名空间中可用的本机方法有什么好处System.Data.SqlClient？Task.Run与仅包含同步方法调用的正文相比，它们有什么优势？

这是我的“起点”示例（控制台应用程序）：

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

class Program
{
    const string CommandTest = @"
SET NOCOUNT ON;
WITH
    L0   AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)), -- 2^1
    L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),       -- 2^2
    L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),       -- 2^4
    L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),       -- 2^8
    L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),       -- 2^16
    L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),       -- 2^32
    Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS k FROM L5)
SELECT
    k
FROM
    Nums
WHERE
    k <= 1000000";

    const string ConnectionString = "Server=.;Database=master;Integrated Security=SSPI;";

    // This requires c# 7.1 or later. Check project settings
    public static async Task Main(string[] args)
    {
        var aSW = new System.Diagnostics.Stopwatch();

        aSW.Restart();
        {
            var aRes = ExecuteSync();
            Console.WriteLine($"ExecuteSync         returned {aRes} in {aSW.Elapsed}.");
        }

        aSW.Restart();
        {
            var aRes = await ExecuteWrapperAsync();
            Console.WriteLine($"ExecuteWrapperAsync returned {aRes} in {aSW.Elapsed}.");
        }

        aSW.Restart();
        {
            var aRes = await ExecuteNativeAsync();
            Console.WriteLine($"ExecuteNativeAsync  returned {aRes} in {aSW.Elapsed}.");
        }
    }

    private static Task<long> ExecuteWrapperAsync()
    {
        return Task.Run(() => ExecuteSync());
    }

    private static long ExecuteSync()
    {
        using (var aConn = new SqlConnection(ConnectionString))
        using (var aCmd = new SqlCommand(CommandTest, aConn))
        {
            aConn.Open();

            using (var aR = aCmd.ExecuteReader())
            {
                long aRetVal = 0;

                while (aR.Read())
                    aRetVal += aR.GetInt64(0);

                return aRetVal;
            }
        }
    }

    private static async Task<long> ExecuteNativeAsync()
    {
        using (var aConn = new SqlConnection(ConnectionString))
        using (var aCmd = new SqlCommand(CommandTest, aConn))
        {
            await aConn.OpenAsync();

            using (var aR = await aCmd.ExecuteReaderAsync())
            {
                long aRetVal = 0;

                while (await aR.ReadAsync())
                    aRetVal += aR.GetInt64(0);

                return aRetVal;
            }
        }
    }
}

谈到我的开发机器的性能，这些*Async方法的使用实际上导致运行时间变慢。通常，我的输出如下：

ExecuteSync         returned 500000500000 in 00:00:00.4514950.
ExecuteWrapperAsync returned 500000500000 in 00:00:00.2525898.
ExecuteNativeAsync  returned 500000500000 in 00:00:00.3662496.

换句话说，该方法ExecuteNativeAsync是使用 的*Async方法的方法，System.Data.SqlClient并且通常比由Task.Run调用包装的同步方法慢。

难道我做错了什么？也许我误读了文档？

标签： c#sql-serverasync-awaitado.net

解决方案

在几乎所有情况下，无论您使用 Sync 还是 Async SqlClient API，都绝对不会对您的查询运行时、聚合资源利用率、应用程序吞吐量或可伸缩性产生有意义的影响。

一个简单的事实是，您的应用程序可能不会进行数千个并发 SQL Server 调用，因此为每个 SQL 查询阻塞线程池线程并不是什么大问题。通过消除请求量的峰值，它甚至可以是有益的。

如果您想从单个线程协调多个 SQL Server 调用，该 API 很有用。例如，您可以轻松地启动对 N 个 SQL Server 中的每一个的查询，然后 Wait() 以获取结果。

在现代 ASP.NET 中，您的控制器和几乎所有 API 调用都是异步的，并且在 UI 应用程序中，使用异步方法非常有用，可以避免阻塞 UI 线程。

推荐阅读