CODEWITHSUNDEEP
mysqlasp.net-web-apinhibernate
Redwood
Redwood

Reputation: 69312

Can NHibernate be configured not to open a new MySQL connection for each query?

We have a web service built using Asp.net Web API. We use NHibernate as our ORM connecting to a MySQL database.

We have a couple of controller methods that do a large number (1,000-3,000) of relatively cheap queries.

We're looking at improving the performance of these controller methods and almost all of the time is spent doing the NHibernate queries so that's where we're focusing our attention.

In the medium term the solutions are things like reducing the number of queries (perhaps by doing fewer larger queries) and/or to parallelize the queries (which would take some work since NHibernate does not have an async api and sessions are single threaded) and things like that.

In the short term we're looking at improving the performance without taking on either of those larger projects.

We've done some performance profiling and were surprised to find that it looks like a lot of the time in each query (over half) is spent opening the connection to MySQL.

It appears that NHibernate is opening a new connection to MySQL for each query and that MySqlConnection.Open() makes two round trips to the database each time a connection is opened (even when the connection is coming from the pool).

Here's a screenshot of one of our performance profiles where you can see these two things:

Performance profile

We're wondering if this is expected or if we're missing something like a misconfiguration/misuse of NHibernate or a way to eliminate the two round trips to the database in MySqlConnection.Open().

I've done some additional digging and found something interesting:

If we add .SetProperty(Environment.ReleaseConnections, "on_close") to the NHibernate configuration then Open() is no longer called and the time it takes to do the query drops by over 40%.

It seems this is not a recommended setting though: http://nhibernate.info/doc/nhibernate-reference/transactions.html#transactions-connection-release

Based on the documentation I expected to get the same behavior (no extra calls to Open()) if I wrapped the reads inside a single NHibernate transaction but I couldn’t get it to work. This is what I did in the controller method:

using (var session = _sessionFactory.OpenSession()) {
    using (var transaction = session.BeginTransaction()) {

        // controller code

        transaction.Commit();
    }
}

Any ideas on how to get the same behavior using a recommended configuration for NHibernate?

After digging into this a bit more it turns out there was a mistake in my test implementation and after fixing it using transactions eliminates the extra calls to Open() as expected.

Upvotes: 1

Views: 595

Answers (2)

Frédéric
Frédéric

Reputation: 9864

Not using transaction is considered a bad practice, so starting to add them is anyway welcome.

Moreover, as you seem to have find out by yourself, the default connection release mode auto currently always translates to AfterTransaction, which with NHibernate (v2 to v4 at least) releases connections after each statement when no transactions are ongoing for the session.

From Connection Release Modes:

Note that with ConnectionReleaseMode.AfterTransaction, if a session is considered to be in auto-commit mode (i.e. no transaction was started) connections will be released after every operation.

So simply transacting your session usages should do it. As this is not the case for your application, I suspect other issues.

Is your controller code using other sessions? NHibernate explicit transactions apply only to the session from which their were started (or to sessions opened from that session with ISession.GetSession(EntityMode.Poco)).
So you need to handle a transaction for each opened session.

You may use a TransactionScope instead for wrapping many sessions in a single transaction. But each session will still open a dedicated connection. This will in most circumstances promote the transaction to distributed, which has a performance penalty and may fail if your server is not configured to enable it.

You may configure and use a contextual session instead for replacing many sessions per controller action by only one. Of course you can use dependency injection instead for achieving this too.

Notes:

About reducing the number of queries issued by an application, there are some easy to leverage features in NHibernate:

Going parallel for a web API looks to me as a doomed road. Threads are a valuable ressource for web application. The more threads a request uses, the less requests the web application will be able to serve in parallel. So going that way will very likely be a major pain for your application scalability.

The OnClose mode is not recommended because it delays connection releasing to session closing, which may occur quite late after the last transaction, especially when using contextual session. Since it looks like your session usage is very localized, likely with a closing very near the last query, it should not be an issue for your application.

Upvotes: 2

Low Flying Pelican
Low Flying Pelican

Reputation: 6054

parallelize the queries (which would take some work since NHibernate does not have an async api and sessions are single threaded) and things like that.

You can defer the execution of the queries using NHibernate Futures,

Following code (extracted from reference article) will execute single query despite there are 2 values retrieved,

using (var s = sf.OpenSession())
using (var tx = s.BeginTransaction())
{
    var blogs = s.CreateCriteria<Blog>()
        .SetMaxResults(30)
        .Future<Blog>();
    var countOfBlogs = s.CreateCriteria<Blog>()
        .SetProjection(Projections.Count(Projections.Id()))
        .FutureValue<int>();

    Console.WriteLine("Number of blogs: {0}", countOfBlogs.Value);
    foreach (var blog in blogs)
    {
        Console.WriteLine(blog.Title);
    }

    tx.Commit();
}

You can also use NHibernate Batching to reduce the number of queries

Upvotes: 1

Related Questions