Asynchronous SQLDataTable from ExecuteReaderAsync - makes sense?

Question

I am using ASP.NET 4.5, with <httpRuntime targetFramework="4.5" /> set.

I have an old data helper library method that returns a SQLDatatable using SQLDataAdapter.Fill().

I want to add a method to return Task(of SQLDatatable) - an async equivalent of the old method.

The code I have is like below (only the portion of code that matters is included):

Note:

GetConnectionString() returns the sql connection string
GetSqlCmd(...) returns a SQLCommand object

LogError() logs any exception

Using conn As New SqlConnection(GetConnectionString())
    Try
        cmd = GetSqlCmd(strSQL, conn, hash)
        Await conn.OpenAsync().ConfigureAwait(False)

        Using datareader = Await cmd.ExecuteReaderAsync().ConfigureAwait(False)
            userDataTable.Load(datareader)
        End Using

    Catch ex As Exception
        LogError(ex)
    End Try

End Using

And the code works in the ASP.NET application, once I add await to the library method call.

The thing I'm not really sure is if the code above makes sense. Certainly the SQLDataReader is async, but SQLDataTable.Load(datareader) doesn't appear to use ReadAsync (as far as I can tell)...

Does this fact make the above code pointless - meaning, "ExecuteReaderAsync without ReadAsync" is pointless?

score 0 · Answer 1 · answered Jul 29 '14 at 09:36

0

It is not pointless. Using async is not all or nothing. In a web app async IO is about block threads less. It is not required to not block them at all.

You have eliminated the blocking resulting from OpenAsync and ExecuteReaderAsync. Draining the reader is still synchronous.

That said, most web apps don't get any benefit at all from using async. For example it does not magically make your queries faster or add more capacity to the database server. So chose wisely.

answered Jul 29 '14 at 09:36

usr

168,620
35
240
369

the application this async method will be used on serves some sites that have close to 1000 concurrent users. I think in this case it makes sense to reduce blocking due to I/O, as much as possible, according to your answer. Thanks for the clarification! – SamuelC Jul 29 '14 at 09:49
1000 concurrent users != 1000 concurrent requests. Your app would crumble under 1000 concurrent requests. You cannot add more capacity by going async. Certainly not to the database. SQL Server executes queries synchronously for example. No problem with that. – usr Jul 29 '14 at 09:57

Dave Black · Answer 2 · 2023-01-13T15:08:53.540

It is not "pointless", rather it helps that specific piece of code scale better. You don't have to use it everywhere but if you need to improve scalability, you should as much as possible.

Async is not about making anything faster, it is about making an app scalable. It is misleading at best (and totally wrong at worst) for this answer to say "...most web apps don't get any benefit at all from using async. For example it does not magically make your queries faster or add more capacity to the database server".

The apps that will get the most from going async are those that require scalability. Here is another answer on SO that has more detail - Not much difference between ASP.NET Core sync and async controller actions

Not using async means your calls are blocking waiting for the db to do something. Using async means those threads that would've been blocking can actually do something else. This increases scalability. Microsoft's own Bing.com is internally run on SqlServer so SqlServer has no problem with scalability when designed and implemented correctly.

Asynchronous SQLDataTable from ExecuteReaderAsync - makes sense?

2 Answers2