How can I run NHibenate queries asynchronously?

Question

One way to increase scalability of the server application is to run IO-bound operation (reading files, sockets, web requests, database requests etc) asynchronously. This does not mean run them in the ThreadPool which will just block threads while operation is being executed. The correct way is to use asynchronous API (BeginRead, BeginGetResponse, BeginExecuteReader etc). The problem is well described in CLR vi C# book.

Here is some article about asynchronous queries in Linq to SQL.

Are any ways to execute Nhibernate query asynchonously? What about Linq to NHibernate?

Thank you, Andrey

Isauraisbel

async support is comming for NHibernate 4.2.0 and 5.0.0. nhibernate.jira.com/browse/NH-3971 – Greenquist

Answer 1

As of NHibernate v5, async is now fully supported!

Here are some nifty examples:

Customer customer = await session.GetAsync<Customer>(1);

List<Customer> customers = await session.Query<Customer>().ToListAsync();

Customer customer = await session.Query<Customer>()
.Where(x => x.Name.Contains("Customer 1"))
.SingleOrDefaultAsync();

Updating an entity

using (ISession session = sessionFactory.OpenSession())
using (ITransaction transaction = session.BeginTransaction())
{
    Customer customer = await session.GetAsync<Customer>(1);
    customer.Name = "Customer 3";
    await session.SaveOrUpdateAsync(customer);
    await transaction.CommitAsync();
}

Source article

Nate

Comment removed and flag retracted :) – Cuss

Answer 2

Note that async database calls do NOT imply better overall scalability by themselves. I recommend reading the article "Should my database calls be Asynchronous?" for an in-depth analysis. Here's a quote from that article:

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.

Greening

this argument became invalid as of August 2012 (after .net 4.5 rtm). now that the cost of async code was significantly reduced it is absolutelly ok to write db operations in async way. but at this very moment there is still not support in NHibernate. hopefully this will be invaldated as well soon :) – Unmitigated

@BorisBucha .NET 4.5 has nothing to do with this. Just because you can more easily express async code in your C# code doesn't mean that web servers and databases are automatically more scalable. – Greening

i believe it does. sure you cannot make your DB faster just by calling it in async way but your webserver is much easier to scale vertically. Less threads mean less memory . If i may drastically oversimplify the topic i could say that by leveraging asynchrony you always win. The performance stays the same in worst case. In best case you can improve it significantly just by forbiding io-bound tasks to occupy Threadpool. And the development cost is almost nothing so i dont see any reason not to do everything in async-way starting from .net4.5. – Unmitigated

@MauricioScheffer u might wanna check ur answer again: tugberkugurlu.com/archive/… – Uproar

@Uproar I think you should put some real load on the database to make a more relevant test. When you do that you'll realize that you're mostly just transferring the load to the database, as described in the article I linked. This is not to say there aren't valid uses of async DB calls, but I suspect they're not as prevalent as many people think. – Greening

@MauricioScheffer did u read the article completely? Quote: In fact, you will see that synchronous version of the operation completes faster than the asynchronous one at some points. The reason is that the SQL query takes small amount of time (approx. 8ms) here to complete.. That post points out both bad and good sides of async db calls. – Uproar

@Uproar Yes, I read it. But I don't see where it puts real load on the database. A delay is, of course, not real load. – Greening

@MauricioScheffer delay was there to simulate the long running db query which was my point. What is the difference? – Uproar

@Uproar the difference is that a delay doesn't make the database use any CPU or I/O. It's not real load. Therefore the experiment doesn't show the load shifting effect described in the article I linked. – Greening

-1 Doesn't answer the question; should be a comment or -1 This answer is no longer relevant. Without any comment on the merits of asynchronous database calls ... from the source quoted in the answer: you should take advantage of asynchronous programming. – Karaganda

@BorisBucha, there is not only the application performance to take into account. There is also the developer performance. The .Net 4.5 async model contaminates all the app stack from the controller down to the data access layer. With async, devs have to take care of what they put in context and whether async calls need to preserve it or not. (Yes I know ideally nothing should be in context, but we are not living in utopia). And this can leads to tedious bugs. So I personally do not consider that transitioning to async is worth it, even with .Net 4.5. – Tripterous

@BorisBucha, this is probably rather late but: async code introduces overheads over sync code, so it can easily be slower. Ayende wrote about this recently. – Timi

Real case scenario: I had three web servers with web api and one mysql db, after just changing api to use async await I was able to scaledown to two websevers and saved money, and this have nothink to do with db load. If you have more user request it is natural that you should think about bigger db load and optimize it or scale. – Manualmanubrium

Answer 3

multiple async calls can be rewritten with Futures

var footask = QueryFooAsync();
var bartask = QueryBarAsync();
var baztask = QueryBazAsync();

var foos = await footask;
var bars = await bartask;
var baz = await baztask;

// do something with foos, bars, baz

can be replaced with

var foos = session.Query<Foo>().....ToFuture();
var bars = session.Query<Bar>().....ToFuture();
var baz = session.Query<Bazes>().....ToFutureValue();

await Task.Factory.StartNew(() => var ignored = baz.Value)  // await the results

// do something with foos, bars, baz

this even has the benefit over async code that the roundtrip time is only paid once instead of 3 times.

Geognosy

This is not true async operation and I would not suggest to do it because now you are just creating yet another thread. Starting new task can be beneficial if you want to ofload cpu bound job to separate thread, but db call is I/O operation and it works best with async api. – Manualmanubrium

@AndzejMaciusovic You are right that it is not really async as freeing the thread but it blocks the thread a lot shorter (1 roundtrip instead of 3) and can lighten the load on the database because it only handles 1 Request (with 3 queries). See Mauricio Scheffer's answer to why saving CPU might not help that much. – Geognosy

Answer 4

Unfortunately, no. NHibernate does not expose the internal implementation of the command execution in the way L2S does.

You'll have to use the threadpool OR create a patch for NH to add asynchronous query support. That would be very welcome by the community and would make for a nice exercise (but it's not trivial at all)

Mistakable

Futures + 1 task does it quite nicely for all databases no? MARS (which is needed for multiple async calls) is not supported (and really needed?) by some sql databases – Geognosy

Futures do not begin execution. They're just a wrapper over multiqueries. – Mistakable

i'm aware of that. Thats why i wrote "+ task". See my answer below for what i mean – Geognosy

But that uses the threadpool, which is what the async patterns try to avoid. Otherwise, you haven't really increased the scalability of your application. – Mistakable

Answer 5

Although there is still no support for async queries in NH, you can still partially overcome some of the undesired effects of running (long-running) db calls from request thread.

What you want is to split Threadpool between short-running and long-running operations. Of course this is not possible with actual implementation of Threadpool and TPL but you can help yourself quite eassilly by writing your own Producer/Consumer queue with awaitable items and customized concurency.

Please have a look at example i have put together : https://gist.github.com/3746240

Code is copy/pasted from great book "C# 5.0 in a Nutshell: The Definitive Reference" by Joseph Albahari and Ben Albahari with modification done by me causing the scheduler to create dedicated worker threads for items proccesing.

Unmitigated

FYI, remember that Threadpool (and "await Task.StartNew(...)" in consequence) has auto-grow feature that when under heavy load you can end up with many dedicated threads and this might be more desirable solution. honestly, i havent done any performance comparison of my proposed solution compared to plain old threadpool with autogrow but i believe it might be helpful in some cases (if you measure perf leak caused by slow autogrow itself :)) – Unmitigated