ExecuteReaderAsync causes sql query to take longer to finish?

Question

I have this SQL server instance which is shared by several client-processes. I want queries to finish taking as little time as possible.

Say a call needs to read 1k to 10k records from this shared Sql Server. My natural choice would be to use ExecuteReaderAsync to take advantage of async benefits such as reusing threads.

I started wondering whether async will pose some overhead since execution might stop and resume for every call to ExecuteReaderAsync. That being true, seems that overall time for query to complete would be longer if compared to a implementation that uses ExecuteReader. Does that make (any) sense?

Answer 1

Whether you use sync or async to call SQL Server makes no difference for the work that SQL Server does and for the CPU-bound work that ADO.NET does to serialize and deserialize request and response. So no matter what you chose the difference will be small.

Using async is not about saving CPU time. It is about saving memory (less thread stacks) and about having a nice programming model in UI apps.

In fact async never saves CPU time as far as I'm aware. It adds overhead. If you want to save CPU time use a synchronous approach.

On the server using async in low-concurrency workloads adds no value whatsoever. It adds development time and CPU cost.

Answer 2

The difference between the async approach and the sync approach is that the async call will cause the compiler to generate a state machine, whereas the sync call will simply block while the work agains't the database is being done.

IRL, the best way to choose is to benchmark both approaches. As usr said, usually those differrences are neglectable compared to the time the query takes to execute. Async will shine brighter in places where it may save resources such as allocating a new thread

There are many posts about async performance:

1. Async await performance
2. The zen of async: best practices for best performance
3. Async Performance: Understanding the Costs of Async and Await