8

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?

Community
  • 1
  • 1
Sean Anderson
  • 27,963
  • 30
  • 126
  • 237

6 Answers6

4

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).

Simon Belanger
  • 14,752
  • 3
  • 41
  • 35
  • Thanks. This was a reasonably helpful explanation. I'm developing a web API, so there's no blocked UI thread, but I can see your point about thread pools. It seems like this is the argument people were making 'for' something like NodeJS where it is handled 'implicitly.' – Sean Anderson Mar 10 '14 at 18:51
3

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.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
1

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

Onur Gumus
  • 1,389
  • 11
  • 27
  • 1
    How come you haven't submitted this as a pull request to NHibernate if you've gotten it working? Just curious. Also, why did you remove all of the IDBCommand interfaces in favor of DBCommand? – Sean Anderson Mar 13 '14 at 03:20
  • 1
    Because it targets .net 4.5.1 (actually I could have rewritten it as targeting .net 4.5) because ExecuteReaderAsync method does not exist in .net 4. So it won't merge to main branch because then nh won't work in .net 4. For DBCommand, in future you will see this will also land in nhibernate too. This is the only way you can invoke Async methods. IDbCommand does not support async – Onur Gumus Mar 13 '14 at 08:31
  • Okay. Cool. Do you have a test case I could use to see if I'm using async properly, or a git repository where I can see a few lines of you using this version of NHibernate? I'll try to give it a spin over this weekend and report back on if I was successful. :) – Sean Anderson Mar 13 '14 at 15:20
  • I haven't commited my tests yet. But it is simple just do `await session.Query().Where(...).ToListAsync();` Note that you have to compile it from Visual Studio, Antr.Runtime is explicitly referenced in this case. No ILMerge (I don't like it) – Onur Gumus Mar 13 '14 at 15:33
  • There are also other async methods like session.GetAsync() – Onur Gumus Mar 13 '14 at 15:35
1

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

Pavel Samoylenko
  • 491
  • 7
  • 14
0

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);
Matt
  • 2,682
  • 1
  • 17
  • 24
  • Entirely correct about the language features. However, asynchronous code has nothing to do with running more threads. When used correctly, no additional threads are required to service eg. a 'waiting' database request beyond the event fired by the kernel when the request completes. Sadly, NHibernate only supports synchronous waits so it's going to eat a thread regardless. – Alex Davidson Aug 19 '16 at 21:07
0

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.

RBarryYoung
  • 55,398
  • 14
  • 96
  • 137