How to properly use async methods on SqlConnection and SqlDataReader?

Question

I am porting some old ASP.NET code to .NET 4.5. I am used to using SqlConnection/SqlCommand/SqlDataReader the old fashioned way. Now I am looking at using async primitives in hopes of better performance.

Here is my old code for opening a connection:

    static DBWrapper Open(string connKey) {
        string connStr = WebConfigurationManager.ConnectionStrings[connKey].ConnectionString;
        SqlConnection c = new SqlConnection(connStr);
        c.Open();
        DBWrapper retVal = new DBWrapper();
        retVal._c = c;
        return retVal;
    }

Class DBWrapper is just a thin wrapper over SqlConnection that has some extra methods I need.

Here is how I converted to the new async style:

    static async Task<DBWrapper> Open(string connKey) {
        string connStr = WebConfigurationManager.ConnectionStrings[connKey].ConnectionString;
        SqlConnection c = new SqlConnection(connStr);
        var v = c.OpenAsync();
        DBWrapper retVal = new DBWrapper();
        await v;
        retVal._c = c;
        return retVal;
    }

Basically, while the connection is being opened, I am able to create a new DBWrapper object concurrently.

What I am confused is if this function really performs better or worse. The overhead of task creation and waiting on it may be much more than the time it takes to create a new DBWrapper object. Whether or not I use async, I am ultimately returning a fully built connection object anyway.

Here is a method that returns the list of users from the database:

 List<MyUsers> getUsers() {
     using(SqlCommand cmd ...) {
       using(SqlDataReader reader ...) {
          while(reader.Read() {
             ... Fill the list
          }
       }
 }

I can convert this method as well into async style as well:

while (await reader.ReadAsync())
  ... Fill the list
}

Once again I don't see any performance gains. In fact, in the second example, I don't even do anything else concurrently while waiting on ReadAsync().

Is this the correct approach or is there a better way to use async paradigm with database connections and queries?

Answer 1

Keep in mind that async does not mean "faster"; it means "concurrent". That is, the point of async is that you can do something else while the operation is going on. In a UI app, this "something else" is normally "responding to user input"; the primary benefit of async for UI apps is responsiveness. For ASP.NET apps, it works a little differently...

If you use async "all the way up", then the primary benefit of async on ASP.NET apps is scalability. By "all the way up", I mean if your List<MyUsers> getUsers() method became Task<List<MyUsers>> getUsersAsync(), and the method that called it becomes async, and so on and so forth all the way out to your MVC action / WebPage method / whatever (which is also async), then you're "async all the way".

In that scenario, then you get a scalability benefit. Note that each individual operation will not run any faster, but your application as a whole will scale better. When await yields to the ASP.NET runtime, the request thread is freed up to handle other requests while that database connection is receiving the next row of data. With the synchronous code, any time your app is waiting on I/O while talking to the database, it's blocking a thread (which is doing nothing); async frees up that thread while waiting for the I/O.

So the app as a whole can scale better, since it is making better use of the thread pool threads.

It's true that sometimes you can get gains by exploiting concurrency within a request; for example, if one request had to access the database and also call some web API, then you could start both of them and use await Task.WhenAll(...) to (asynchronously) wait for both results. In your first code example, you're doing the db connection and an object creation concurrently, but I suspect you won't really see any benefit in that situation unless your constructor is really computationally expensive. If you have two I/O operations or a real CPU operation, then async could help you do concurrency within a request, but the primary use case is to improve scalability of the application as a whole.