Performance of DB async call

Question

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.

For starters don't rethrow the exception on the catch block after you handle it and retry the query. — D Stanley
– D Stanley, Commented Dec 14, 2016 at 3:30
The function then needs to return something else; I want to leave it unhandled so it can potentially be caught further up (Unless there's a better way of doing it that I'm missing). — Hele
– Hele, Commented Dec 14, 2016 at 3:34

sellotape · Accepted Answer · 2016-12-14 08:28:06Z

1

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.

answered Dec 14, 2016 at 8:28

sellotape

8,4152 gold badges28 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hele Over a year ago

Does this still apply if the network call is over localhost?

sellotape Over a year ago

@Hele: yes - the database's (likely) disk I/O and the inter-process communications required will still far exceed any async overhead.

Aleksandr Zolotov · Accepted Answer · 2016-12-14 05:47:49Z

0

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...

answered Dec 14, 2016 at 5:47

Aleksandr Zolotov

1,1303 gold badges20 silver badges33 bronze badges

Collectives™ on Stack Overflow

Performance of DB async call

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related