Does Asynchronous processing actually help if in case we have database calls into it?

Question

In a server, if I have a async function which will process some database calls and fetch some records from the table. If the server receives high number of requests, that database call might actually be blocking and we don't really have a async working there.

1. When we have some database calls in a server process, does async methods help ?

2. If not, what are the problems that will arise because of such methods ?

I don't want to customise it to a particular database because then my question will deviate. I just want to revolve around asynchronous programming paradigm here. If there are suggestions on some optimised way for the database fetching operation, then its welcome. I would just like to know for a normal fetch for the start.

Answer 1

In concurrent applications under load, asynchronous method calling can improve performance and throughput, because the calling thread is freed up to do other work.

There are edge cases where it will not help, but if your application spends any significant amount of time doing anything other than waiting for data from the database, you should see some benefit. These benefits apply to both server side and GUI applications.

For example, if your application spends 300ms waiting for data, and 300ms processing it, and you are dealing with a large number of simultaneous requests, you could potentially double throughput by using async calls, because each thread requesting data will be freed up instantly, and can then deal with data returned by a previous request.

You will rarely see this degree of improvement in practice, because requests do not arrive at nice even intervals, and generally do not each require the same amount of work. The difference should be noticeable though.

However, if you spend 300ms waiting for data, and only spend 10ms processing it, you will not see anywhere near as much improvement.

In an environment such as ASP.Net, this is very important as there are a limited number of threads available to process all incoming web requests: If all the threads are waiting on the database server, then no pages are served.

In windows forms applications, using Async calls to fetch data allows the UI to remain responsive while the data is fetched, allowing you to give the user the option of cancelling if they get bored waiting.

The obvious downside is that it makes the calling code significantly more complicated.

Answer 2

You have two potential problems here, the first can be resolved by using async methods the second can only be resolved by tuning your database.

Using async methods will allow your clients to perform other processing whilst waiting for your server, i.e. remain responsive in the case of a UI.

The database locking issue can only be solved by analysing the database under load to see which tables are being locked and which stored procs or processes are causing the locks.

You might want to look into in memory caching or other optimisations if you really do have a need for a huge amount of requests.

Answer 3

Using async calls will certianly help your throughput in the situation you're descripting if you have other work for the calling thread to perform. For example, if Thread 1 handles a request to the server & makes a database call, even if you use a .BeginInvoke(res) followed immediately by .EndInvoke(res) you're still blocking on that call.

To scale properly you need to make sure that Thread 1 has other work to do, perhaps preparing another page request until it's database call is done. This can be accomplished by using a shared work queue or sequential queues like the pipeline pattern. Check out some of the links in this question for more details about async design patterns and how they can help you get better throughput out of your server.

Design Patterns [1]: Resources about Asynchronous Programming Design Patterns

Answer 4

To keep the answer short:

Using asynchronous processing helps in particular when you have database calls.

In a server application you should avoid blocking threads. If in typical request your server accesses the database, then if you do this synchronously, it means that your threads are blocked waiting for IO most of the time. When enough threads get blocked, this will introduce a bottle neck on your server and hurt scalability, as no thread-pool threads are available to process new requests, and in addition your CPU may get under-utilized as no threads are available to pick up work to do.