CODEWITHSUNDEEP
asp.netasp.net-mvcazureasynchronousazure-sql-database
Matt Roberts
Matt Roberts

Reputation: 26897

Making my controller DB calls Async or not

I've inherited a MVC web application, that's using Dapper on top of ADO.NET to make DB calls in the Controller action methods. It's all pretty standard stuff - not many of the controlers are async, and all the database calls go through a repository which is fully synchronous.

I'm moving this to Azure, and I'm using SQL Azure for the database back end. I'm expecting load to be fairly standard - say 500 - 1000 hits per minute.

So, I'm wondering, should I be ploughing through this code to make all my db calls async, so that i can await then in the controllers. Doing this is going to free up my threads to serve up other requests, but I'm wondering in real terms if I'm going to notice any improvement.

I know that previously, it's been noted that if you have a single db server (as I do), then you won't really see much improvement, because the bottleneck is all on the db. However, SQL Azure is a slightly different beast, and Azure state that

Good practice demands that you use only asynchronous techniques to access Azure services such as SQL Database source

So - is this worth the effort?

Upvotes: 2

Views: 1185

Answers (2)

Clint B
Clint B

Reputation: 4710

What I've learned (and verified through testing) is that you will not see a great improvement on your relatively long SQL calls. But you will see improvement on concurrent short SQL and non-SQL related responses. That is because there is a significant cost to initializing a thread. So reusing the dormant threads that are waiting for SQL does increase performance.

Using async also protects you from going over "Threads Per Processor Limit" setting in IIS. When that happens your requests get queued. We have experimented increasing the default value of 25. This did improve performance under high load but we saw better improvements by changing all our controllers to async.

So I guess the answer to your question is, it depends. If you have a significant number of concurrent requests other than your SQL calls, you should see a noticeable improvement on the response time of those concurrent requests. But you won't see much of an improvement on the relatively long SQL calls.

Upvotes: 1

Chris Pratt
Chris Pratt

Reputation: 239430

Frankly, it's impossible to give any sort of definitive answer here. As Azure states, best practice is to use async with I/O-bound operations, including things like querying a remote database. If you were starting this application from scratch, today, I'd definitely tell you use async for your database calls.

However, this is not a new application, and it sounds like using async will require quite a bit of surgery at this point. Depending on the load you get, you may not end up seeing any gains for the work, but you might also see great gains. My recommendation is to start small. I would pick out some of the more longer-running queries you make or actions that rely on the database heavily and start with those. That way you can introduce a bit of async and judge for yourself whether it's worth pursuing it further. And, since these are likely to be the bottlenecks of your application, anyways, you gain the benefits of async where it will potentially matter the most.

Any new functionality you add should be async from the start, and then, simply when you have the time and inclination, work slowly on converting the whole application.

Upvotes: 2

Related Questions