CODEWITHSUNDEEP
c#async-await
Hele
Hele

Reputation: 1578

Performance of DB async call

I have the following code:

public static async Task<uint?> GetJobAsync(string name)
{
    return await Program.Database.PerformQueryAsync<uint?>(async (command) =>
    {
        command.CommandText = "SELECT `job` FROM `people` WHERE `name`=@name;";
        command.Parameters.Add(new MySqlParameter("name", name));
        MySqlDataReader reader = await command.ExecuteReaderAsync() as MySqlDataReader;
        if (!reader.HasRows)
            return null;
        reader.Read();

        return reader.GetUInt32(0);
    });
}

public async Task<T> PerformQueryAsync<T>(Func<MySqlCommand, Task<T>> operationAsync)
{
    try
    {
        using (MySqlCommand command = CreateCommand())
            return await operationAsync(command);
    }
    catch (MySqlException ex)
    {
        if (ex.GetBaseException() is SocketException)
        {
            Console.WriteLine("MySQL connection was broken. Reconnecting...");

            if (!IsConnected())
                Connect();

            using (MySqlCommand command = CreateCommand())
                return await operationAsync(command);
        }

        throw;
    }
}

The second function exists as the underlying MySQL connection often wait_timeouts. My SQL query is short and takes less time. However, 3 async methods are called to fetch this information. Is this a good use case of async? Should I convert to synchronous code?

My concerns arise from the suggestion in this Microsoft magazine article showing the Il code generated for a simple async call.

Upvotes: 1

Views: 159

Answers (2)

sellotape
sellotape

Reputation: 8325

The answer is it depends, and mostly on your context. If those methods are running in the context of something with an active SynchronizationContext; e.g. WinForms, ASP.NET, WPF, then you should almost certainly prefer async, so as not to block the main thread. This is especially relevant in your case, as you imply your database action often times out, during which the main thread would otherwise be blocked that whole time.

Stephen Toub's article you link to is - as always - excellent, but as he also says - especially in his other articles - the async overhead is completely insignificant compared to the cost of most I/O operations, especially a network call to your database.

Upvotes: 1

Aleksandr Zolotov
Aleksandr Zolotov

Reputation: 1100

I think you should not convert to synchronous code - because asynchronous give you better performance. In that case your code don't blocked - so system will use less threads...

Upvotes: 0

Related Questions