Partager via

Should my database calls be Asynchronous?

  • Article

Update 28 November 2012: The combination of await, async, and the Task object makes it much easier for you to write asynchronous code in .NET 4.5.  Now that EF 6 is supporting Async Query and Save, you should take advantage of asynchronous programming. (As of this update, EF6 is not released so don’t use it just yet for production code).  When this blog was first written, it was much more difficult to wire asynchronous code. See my two async tutorials:

Long running/expensive database calls might seem like a natural candidate for asynchronous calls. In my MSDN article, Using an Asynchronous Controller in ASP.NET MVC, I go out of my way to avoid suggesting DB calls are good candidates.  Conventional wisdom (AKA text-book thinking) suggests expensive DB calls should be made asynchronously - but I assert they should almost never be.  Performing an async operation is vastly more complicated than performing a sync operation.  If you want to see something truly frightening, compare the stack traces of a synchronous MVC action with an asynchronous MVC action. The major exception to this is when you have multiple DB calls to make in parallel, in that case aSync can be the clear winner.

The following is a partial list of links by well established ASP.NET Gurus suggesting async is the way to go for expensive DB calls. (Most of the links are excellent reads  except for the bad advise about making DB calls async).

Suppose you have the following system:

  • A web application running on IIS 7 /.NET Framework v3.5 SP1 using the default request gating (maxConcurrentRequestsPerCPU="12")
  • Your server is a dual proc (two processors).
  • You are not using asynchronous methods.
  • Your web application contains a mix of static content, quick running dynamic pages, and a few very slow pages that hit the DB. (The DB is the bottleneck on these slow pages.)

With the above configuration, you are limited to 24 concurrent requests. While your web server can easily handle the average work load, under bursty conditions, you end up with 30 concurrent calls to the slow DB pages. Once the slow DB pages tie up the 24 threads (which are not doing useful work, they are just waiting for the DB to respond), all new requests are queued and your customers are waiting, even though your CPU is free. This may sound like the perfect time to use asynchronous calls, but there are a couple of problems. The maxConcurrentRequestsPerCPU setting does request gating, so converting the slow DB calls to async won’t help (without changing this setting). If you do convert these slow methods to async, you should follow the IIS/ASP.NET performance guru Thomas Marquardt’s advice and set maxConcurrentRequestsPerCPU = "5000" and maxConcurrentThreadsPerCPU="0".

Rather than convert your blocking DB calls to asynchronous, you can use thread gating by setting maxConcurrentThreadsPerCPU=30. Now when this same app is hit by 30 concurrent slow DB requests, it will have 30 threads free to serve up the cheaper requests. It’s a lot easier to change this setting than to convert your working DB code to use asynchronous approaches.

The IIS thread pool can often handle many more simultaneous blocking requests than a database server. If the database is the bottleneck, asynchronous calls will not speed up the database response. Without a throttling mechanism, efficiently dispatching more work to an overwhelmed database server by using asynchronous calls merely shifts more of the burden to the database. If your DB is the bottleneck, asynchronous calls won’t be the magic bullet. You need to add spindles or make your queries more efficient.

One respected DB/Web architect went so far as to say:
For database applications using async operations to reduce the number of blocked threads on the web server is almost always a complete waste of time. A small web server can easily handle way more simultaneous blocking requests than your database back-end can process concurrently. Instead make sure your service calls are cheap at the database, and limit the number of concurrently executing requests to a number that you have tested to work correctly and maximize overall transaction throughput. 

Required reading:

If you observe that the “ASP.NET ApplicationsRequests in Application Queue” performance counter is non-zero, you definitely have a performance problem. (From Thomas’s Blog).

Comments

  • Anonymous
    December 06, 2010
    The comment has been removed

  • Anonymous
    August 18, 2011
    The current async fad is unbelievable (for example node.js). Never seen someone speak the truth (like in this post). thx.

  • Anonymous
    June 09, 2013
    How can we do this for IIS 8?

  • Anonymous
    April 15, 2014
    unless you are using DBaaS with virtually no bottleneck (from your point of view) and REST API then you can greatly benefit in your app server from asynch db access.

  • Anonymous
    May 27, 2014
    What you say makes big sense to me, but why am I seeing so many tutorial examples (see msdn.microsoft.com/.../jj819165.aspx and www.asp.net/.../async-and-stored-procedures-with-the-entity-framework-in-an-asp-net-mvc-application). Neither of those examples mention what you are saying, other than one comment on needing to test performance, but nothing about maxConcurrentRequestsPerCPU etc. Has more testing suggested that await/async are worth it for EF6? I ask this because I am building a generic CRUD service package and wonder if I should put in async versions of each of my services.

  • Anonymous
    September 07, 2014
    If as you say there's no advantage to it then why did they add it to EF6? There seems to be a lot of Cross thinking going on here as Jon points out.

  • Anonymous
    October 13, 2014
    First, I think it's important to point out that async is not for "long-running processes". Async is all about never having idle threads, but in order for async to function a thread has to go into a wait state. It is at that point that the thread is thrown back to the pool for some other work to be done on it whilst whatever task it was waiting on completes. You could do some CPU bound work like financial analysis that can be "long-running", but async will purchase you nothing there. It is for this exact reason that it's good to use async with database queries. It has nothing to do with how big or complex the query is, but rather, the fact that in a typical setup, you must establish a remote connection to the database, sending the query to the database, and receiving the response from the database over a network which may contain degrees of latency. This latency is where your thread will spend most of it's time tapping it's toes and you might as well allow it to be used for something else in the meantime. In ideal scenarios and a good quality network infrastructure, the latency may be so low as to make async not give much of a ROI, but things aren't always ideal and infrastructure can fall down. Async in these times buys you extra CPU cycles on the server which may make all the difference in whether your web server can weather a temporary network disruption or whether it dead locks and starts denying requests.

  • Anonymous
    February 04, 2015
    The comment has been removed

  • Anonymous
    March 13, 2015
    The comment has been removed

  • Anonymous
    November 19, 2015
    The comment has been removed