Using async-await for database queries -- how does that save threads?

Question

I'm starting to understand the concept of how awaiting a chain of async methods that has "at the bottom" a hardware task like writing to a file (There Is No Thread https://blog.stephencleary.com/2013/11/there-is-no-thread.html). But, what is the point of awaiting a database call, like ExecuteQueryAsync, if the database is a local one? Isn't that technically a CPU-bound task since SQL server needs a thread to execute the query?

Answer 1

Your process has a limited number threads in the thread pool (you can create more threads but they're expensive in terms of resources). When you make a database call, the database server is typically a separate process with its own set of threads. It takes time to prepare the data you're asking for so you have some options as to what to do while that's happening:

• Go into a blocking wait - Thread.Sleep, Task.Wait, etc. This means that you fire your database request on thread A and then enter a wait on that thread: the thread is allocated but is blocked and not usable for anything else. When your data is ready, you somehow learn of that and exit the wait and your code continues on thread A.

• You perform an asynchronous wait: you fire your database request and then do an await with a callback function. The call gets sent to the database process but then it just returns (internally it takes note of your callback function (the continuation)). At this point now, thread A is free - you're not using it anymore so it goes back into the pool and it can be used for something else. The database server uses one of its own threads to get your data but that's none of your business - thread A can be used within your own application to do something now.

  A while later, your data is ready and your callback function gets called on some thread - not necessarily thread A and your code can resume.

Async processing thus freed up one of your threads for a bit of time to do something else. You don't get your data faster - it still takes X milliseconds to prepare your data. What you get is more efficiency in using your existing threads and more paralellism for operations that mostly wait, rather than compute (that is, IO operations). In your case you mostly wait - the processing and computing is done by a different process.

As @ErikPhilips pointed out, Stephen Cleary's article is a good read (not just this one): https://blog.stephencleary.com/2013/11/there-is-no-thread.html