Why asynchronous code is so much slower than synchronous code

Question

I create a test table in SQL Server like this:

CREATE TABLE Person
(
    PersonID INT NOT NULL IDENTITY,
    PersonName NCHAR(500) NOT NULL,
    PersonFamily NCHAR(500) NOT NULL,
 
    CONSTRAINT PK_Person PRIMARY KEY(PersonID)
)

Then I create two methods to test async.

The first method uses an async approach to read all person, and the second one uses a sync approach.

Async method:

private async Task<List<Person>> ReadPersonsAsync()
{
    List<Person> result = new List<Person>();

    using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
    {
        using (SqlCommand sqlCommand = new SqlCommand())
        {
            sqlCommand.CommandText = "SELECT * FROM Person";
            sqlCommand.CommandType = System.Data.CommandType.Text;
            sqlCommand.Connection = sqlConnection;

            await sqlConnection.OpenAsync();

            using (var reader = await sqlCommand.ExecuteReaderAsync())
            {
                while (await reader.ReadAsync())
                {
                    result.Add(new Person
                    {
                        ID = int.Parse(reader[0].ToString()),
                        Name = reader[1].ToString(),
                        Family = reader[2].ToString()
                    });
                }
            }
        }
    }

    return result;
}

Sync method:

private List<Person> ReadPersons()
{
    List<Person> result = new List<Person>();

    using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
    {
        using (SqlCommand sqlCommand = new SqlCommand())
        {
            sqlCommand.CommandText = "SELECT * FROM Person";
            sqlCommand.CommandType = System.Data.CommandType.Text;
            sqlCommand.Connection = sqlConnection;

            sqlConnection.Open();

            using (var reader = sqlCommand.ExecuteReader())
            {
                while (reader.Read())
                {
                    result.Add(new Person
                    {
                        ID = int.Parse(reader[0].ToString()),
                        Name = reader[1].ToString(),
                        Family = reader[2].ToString()
                    });
                }
            }
        }
    }

    return result;
}

When I use the async approach it takes 10 seconds, but the sync approach is run in 2 seconds.

Which part of my code is wrong? Thanks.

Async/await is not so much about running code **faster**, but much more about making servers more scalable, e.g. running more concurrent code in a performant fashion ... — marc_s, Jan 10 '22 at 10:00
Notice that if you run same query twice, first execution will spend some time to compile execution plan(SQL Server) where second query will use already compiled one. Difference 10 and 2 seconds is not because of asynchronous — Fabio, Jan 10 '22 at 10:16
Something is rotten in the state of SqlClientland. [Horrible performance using SqlCommand Async methods with large data](https://stackoverflow.com/questions/42415969/horrible-performance-using-sqlcommand-async-methods-with-large-data), [Reading large data (binary, text) asynchronously is extremely slow](https://github.com/dotnet/SqlClient/issues/593), [SQL Reader Async 10 times slower than sync](https://github.com/dotnet/runtime/issues/25329) — Theodor Zoulias, Jan 10 '22 at 10:23

score 1 · Answer 1 · answered Jan 10 '22 at 12:39

1

The answer is that the async reader is simply less efficient than the "normal" reader in the version of Dot Net that you have.

sqlCommand.ExecuteReaderAsync()

is simply slower than

sqlCommand.ExecuteReader()

So for best performance you should read the data synchronously, or choose a different framework.

answered Jan 10 '22 at 12:39

OwlFace

31
5

The time difference between sync and async would only be milliseconds. An 8 second difference is too large to be explained by sync/async alone. – Gabriel Luci Jan 10 '22 at 18:55
In theory, if both the async and the sync function were coded in the exact same way, optimised to the exact same amount and called the exact same subroutines as one another, then yes. In practice, the two functions are coded differently and one works much more efficiently than the other. – OwlFace Jan 12 '22 at 09:13

Why asynchronous code is so much slower than synchronous code

1 Answers1