Benefits of async SqlClient methods

Question

What are the benefits of native *Async methods available in the System.Data.SqlClient namespace? What are their advantages over a manual Task.Run with a body comprised of only synchronous method calls?

Here's my 'starting point' example (console application):

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;
            }
        }
    }
}

Speaking about performance on my development maching, usage of the *Async methods actually resulted in slower running times. Typically, my output was as follows:

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

In other words, the method ExecuteNativeAsync is the one using the *Async methods of System.Data.SqlClient and was most often slower than a synchronous method wrapped by a Task.Run call.

Am I doing something wrong? Maybe I am mis-reading the documentation?

Answer 1

In almost all scenarios whether you use the Sync or Async SqlClient APIs will have absolutely no meaningful impact on your query runtime, aggregate resource utilization, application throughput, or scalability.

The simple fact is that your app is probably not making many thousands of concurrent SQL Server calls, and so blocking a thread pool thread for every SQL Query is not a big deal. It can even be beneficial by smoothing out spikes in request volume.

The API's a useful if you want to orchestrate multiple SQL Server calls from a single thread. For instance you can easily kick off a query to each of N SQL Servers, and then Wait() for the results.

In modern ASP.NET your controllers and almost all of your API calls are async, and in a UI application its a useful to use Async methods do avoid blocking the UI thread.

Answer 2

I have modified the above example and was able to actually benefit from using *Async methods:

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

class Program
{
    const string CommandTest = @"
SET NOCOUNT ON;
WAITFOR DELAY '00:00:01';
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 <= 100000";

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

    const int VirtualClientCount = 100;

    // 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 aTasks = Enumerable.Range(0, VirtualClientCount).Select(_ => ExecuteWrapperAsync());
            await Task.WhenAll(aTasks);
            Console.WriteLine($"ExecuteWrapperAsync completed in {aSW.Elapsed}.");
        }

        aSW.Restart();
        {
            var aTasks = Enumerable.Range(0, VirtualClientCount).Select(_ => ExecuteNativeAsync());
            await Task.WhenAll(aTasks);
            Console.WriteLine($"ExecuteNativeAsync  completed 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;
            }
        }
    }
}

Now I'm getting the following output:

ExecuteWrapperAsync completed in 00:00:09.6214859.
ExecuteNativeAsync  completed in 00:00:02.2103956.

Thanks to David Browne for the hint!

Answer 3

To understand the benefits of Async you need to simulate a server under heavy load with asynchronous operations that take some time to complete. It is virtually impossible to measure the benefit in an application that's running in a production environment without writing two versions of it.

Instead of calling a database that again is under no load, and is presumably local to the application, you can simulate your expected query delays.

As the number of clients, or the length of the operation increases ExecuteAsync will significantly outperform ExecuteSync. Under no load the benefits of using Async are not observed, which is usually the case for the majority of applications running on the majority of servers.

The benefit of Async here is that it releases the thread back to the pool until the asynchronous operation completes, freeing system resources.

The test program:

static void Main(string[] args)
{
    RunTest(clients: 10,   databaseCallTime: 10);
    RunTest(clients: 1000, databaseCallTime: 10);
    RunTest(clients: 10,   databaseCallTime: 1000);
    RunTest(clients: 1000, databaseCallTime: 1000);
}

public static void RunTest(int clients, int databaseCallTime)
{ 
    var aSW = new Stopwatch();

    Console.WriteLine($"Testing {clients} clients with a {databaseCallTime}ms database response time.");

    aSW.Restart();
    {
        Task.WaitAll(
            Enumerable.Range(0, clients)
                .AsParallel()
                .Select(_ => ExecuteAsync(databaseCallTime))
                .ToArray());

        Console.WriteLine($"-> ExecuteAsync returned in {aSW.Elapsed}.");
    }

    aSW.Restart();
    {
        Task.WaitAll(
            Enumerable.Range(0, clients)
                .AsParallel()
                .Select(_ => Task.Run(() => ExecuteSync(databaseCallTime)))
                .ToArray());

        Console.WriteLine($"-> ExecuteSync  returned in {aSW.Elapsed}.");
    }

    Console.WriteLine();
    Console.WriteLine();
}

private static void ExecuteSync(int databaseCallTime)
{
    Thread.Sleep(databaseCallTime);
}

private static async Task ExecuteAsync(int databaseCallTime)
{
    await Task.Delay(databaseCallTime);
}

My results:

Testing 10 clients with a 10ms database response time.
-> ExecuteAsync returned in 00:00:00.1119717.
-> ExecuteSync  returned in 00:00:00.0268717.


Testing 1000 clients with a 10ms database response time.
-> ExecuteAsync returned in 00:00:00.0593431.
-> ExecuteSync  returned in 00:00:01.3065965.


Testing 10 clients with a 1000ms database response time.
-> ExecuteAsync returned in 00:00:01.0126014.
-> ExecuteSync  returned in 00:00:01.0099419.


Testing 1000 clients with a 1000ms database response time.
-> ExecuteAsync returned in 00:00:01.1711554.
-> ExecuteSync  returned in 00:00:25.0433635.