CODEWITHSUNDEEP
c#.netsql-serverasynchronous
Sean Anderson
Sean Anderson

Reputation: 29291

Why are asynchronous calls to my database desireable?

I've written a server which interacts with an MSSQL database. It's currently written in .NET 4.0 and uses NHibernate as an ORM to retrieve information from the database. When reading about .NET 4.5 and the introduction of the async/await keywords I learned that, unfortunately, NHibernate does not have support for async/await .

I don't understand why issuing an async call to a database would be beneficial. Don't all the requests queue at the database level anyway? Wouldn't async just increase points of failure without improving anything?

Upvotes: 8

Views: 3923

Answers (6)

Pavel Samoylenko
Pavel Samoylenko

Reputation: 521

Good news. NHibernate supports async/await out of the box since v 5.0

Upvotes: 1

RBarryYoung
RBarryYoung

Reputation: 56725

You say:

Don't all the requests queue at the database level anyway?

If by "queue" you mean "single-servicing queue" than the answer is no. SQL Server is a highly asynchronous and multi-threaded service that can service many, many queries simultaneously.

Even at a physical level, queueing (i.e. physical device servicing) is simultaneously split across the number of CPU cores, and the number of physical disks the make up the disk array.

So the reason to make asynchronous calls to SQL Server is to be able to leverage some of that multi-threading/multi-servicing capacity into your own service.

Upvotes: 0

Onur Gumus
Onur Gumus

Reputation: 1439

NHibernate can support true async calls. I already implemented it on my own branch

https://github.com/ReverseBlade/nhibernate-core/tree/nh_4.5.1

You can check it out and compile. It is compiled against .net 4.5.1. It is compatible with standart nhibernate and passes all tests. Then you can use things like .ToListAsync(); or GetAsync(), it will make true async calls.

If you need help you can write a comment. Good luck

Upvotes: 1

Remus Rusanu
Remus Rusanu

Reputation: 294267

Don't all the requests queue at the database level anyway?

No. Read Understanding how SQL Server executes a query. Any database server worth the name will be able to run hundreds of requests concurrently. Serialization is necessary only if the requests are correlated (eg. you need the output of query 1 to pass as a parameter to query 2) or when operating under transaction constraints (only one statement can be active at any time within a transaction).

There are at least two major advantages of async calls:

  • resource usage. W/o considering anything else, just changing the programming model to an event driven async model will result in order of magnitude increase of throughput you app can drive. This, of course, applies to back end apps (eg. a web server), not to a client user driven app that will not be able to send anything more than what the one user initiates. Read the articles linked from High Performance Windows programs. This is also important to read, even though a bit dated: Asynchronous Pages in ASP.NET 2.0

  • overlapping requests. The synchronous model doe snot allow to issue a query to the back end until the current one completes. A lot of times the application has the info necessary (the params) to make two or more uncorrelated requests, but it simply can. Doing async calls allow the controlling thread to issue all the request is parallel, and resume after they all complete.

Neither .Net 4.5 Tasks not NHibernate have good support for async DB programming. Good old BeginExecuteXXX is much more powerful actually, although a bit arcane to program against.

Upvotes: 3

Simon Belanger
Simon Belanger

Reputation: 14870

In general, the benefit is that you are not blocking the currently executing thread while a possibly expensive (asynchronous) operation is run. In the context of a WPF / Windows Form application, this mean you are not blocking the UI Thread (if the request is originating from that thread) and your application remains responsive.

In the context of a web application (say IIS), this mean you are releasing a thread in the pool while you are awaiting for the result. Since you are not locking the thread, it can be reused to accept another request and results in better performance in terms of accepted connections (not necessarily time / request).

Upvotes: 4

Matt
Matt

Reputation: 2682

You may be confusing language features with design pattens; async is syntactic sugar to help you manage background tasks, while asynchronous tasks just mean that you're running two or more threads.

Just because NHibernate doesn't support async doesn't mean that you can't run asynchronously. This is very beneficial to the user because you don't want to freeze the UI while you're performing a (relatively) long-running query to a DB/service, especially if the server is bogged down.

I suppose you could count this as a point of failure, but really just a few areas:

  1. Exceptions - You'd have this problem on one thread anyway, but you should gracefully handle any database errors that you'd encounter.
  2. UI Management - You don't want to let the user interact with the UI in such a way as to trigger multiple queries, so you might disable a button, etc.
  3. Result Handling - When the query is complete, you need to ensure that you marshal the data back to the UI thread. In C# this can be done via Invoke/BeginInvoke, though whether you're in WinForms or WPF determines the details.

EDIT:

Some sample skeleton code assuming WPF and at least .NET 4.0

Task.Factory.StartNew(() =>
{
    using (var client = new dbClient())
    {
        // Perform query here

        this.Dispatcher.BeginInvoke(new Action(() =>
        {
            // Set data source, etc, i.e.
            this.Items = result;
        }));
    }
}).ContinueWith(ex => Logger.LogException(ex), TaskContinuationOptions.OnlyOnFaulted);

Upvotes: 0

Related Questions