NHibernate Performance – Lazy Loading

When I initially started using NHibernate I had no clue about it’s common pitfalls and how to avoid them. I learned the hard way that if you don’t optimize your NHibernate queries and mappings, you are going to have performance issues.

One of the most common pitfalls is not telling NHibernate to fetch the data your application needs. NHibernate has a feature called lazy loading that can negatively impact the performance of your application.

In this blog post I will demonstrate how lazy loading could potentially ruin the performance of your application. I will show you how you can analyse any problems caused by lazy loading. After that, I will show you how you can solve any performance problems that are caused by lazy loading..

What is lazy loading?
The lazy loading mechanism basically means that our mapped entities do not contain all the data of the entity, but they know how to retrieve the data should the application need it. By default, all relationships on our mapped entities are lazy loadable. That means that whenever our application want to access a property that references a foreign key relationship or child collection, NHibernate will execute a query to retrieve the requested data. This can lead to horrible performance problems when our application attempts to access alot of a properties that are lazy loadable.

Demo project:
To demonstrate the impact that lazy loading can have on your application performance, I have created a small demo-project. The demo-project has a domain model that consists of three entities: users, projects and roles. Users can have multiple projects and roles and projects and roles can belong to multiple users.

The purpose of this demo application is to output a list of all users along with their roles and projects. You can fetch the code for this demo-project on it’s GitHub repository.

Database diagram of our demo application
Database diagram of our demo application

As you can see in our database design, users have a many-to-many association with Roles and Projects. In order to demonstrate that lazy loading can cause performance problems, I have inserted 3000 users into this database.

Our entities and mappings are fairly straightforward:

Now, for the actual code of our application. For simplicity we are using a simple console application that outputs the user’s first name, last name, projects and roles. Normally we would probably use the repository pattern to retrieve the users instead of directly accessing the NHibernate session, but for the sake of simplicity I have put all our code directly into the Program class of our demo project.

Analyzing NHibernate queries
Now, if we run this code snippet, the console will open and start outputting user information. If you are also running the demo-application, you might notice that it takes quite a while to output all of the users information. In fact, on my computer the program ran for approximatly 1 minute and 43 seconds!

In order to analyse why outputting the users is taking so long, we will have to analyse the queries NHibernate is sending to our database. There are several ways to monitor these queries. The easiest way is to use the built-in Diagnostic Tools of Visual Studio, but you can als use SQL profiler or Express Profiler.

The lazy loading mechanism doing what it does best - firing a ridicilous amount of queries
The lazy loading mechanism doing what it does best – firing a ridicilous amount of queries

This is NHibernate lazy loading mechanism in action. Remember, I added 3000 users into the database. Since we are outputting the roles and projects of every user in the database, NHibernate will fire two database queries per user. One query to load the projects of a user and one query to load the roles of a user.

Solving the problem
Using the NHibernate Linq extension, we can use the Fetch and FetchMany methods to tell NHibernate which properties it should eagerly load from the database.

The Fetch method should be used to retrieve references, while the FetchMany method is used to fetch collections. Since we want to retrieve the project and the role collections, let’s go ahead and add the first FetchMany statement to our NHibernate query.

This will tell NHibernate to load the users and all of their projects. Now if we use Diagnostic Tools again, we will see that NHibernate will only fires extra queries to retrieve the roles of each user.

Now we are getting somewhere!
Now we are getting somewhere!

You might be thinking: That’s great! Let’s add another FetchMany statement to load all the roles and be done with this problem!

Unfortunatly, using multiple FetchMany statements is not allowed. When we run this code, NHibernate will throw the exception: Cannot simultaneously fetch multiple bags.

NHibernate doesn't like multiple FetchMany statements
NHibernate doesn’t like multiple FetchMany statements

Solving the problem – Part 2
In order to solve this exception, we have to split up our query and use the ToFuture Linq extension method. The ToFuture method tells NHibernate that it should not query the database yet, because we want to fire multiple queries and we want NHibernate to bundle them into a single request.

Our new code will look like this:

Analyzing our queries once again
If we take another look at the queries NHibernate fires, we will see that NHibernate only sends a single request to the database that contains only two queries!

The first query retrieves the users and all of their projects and the second query retrieves all of the roles.

If we run our application again we will notice that the performance of our application has improved dramatically. Initially, our application took 1 minute and 43 seconds to complete. After tweaking our database query to use FetchMany statements, the program completes in two seconds!

Woohoo, only a single request to the database!
Woohoo, only a single request to the database!

Final thoughts
Keep in mind that lazy loading is not a good or a bad thing. Lazy loading is a good default behaviour. But as a software developer using NHibernate you need to be aware of the lazy loading mechanism. Analyzing the queries NHibernate sends to your database is the best way to determine which data to eagerly load.

You can find the complete code of the demo-project on my GitHub repository.

3 thoughts on “NHibernate Performance – Lazy Loading”

  1. I am happy to see devs still blogging about NHibernate as I think it is still #1 ORM you get in .NET for use in SQL-heavy systems. I have been using it for years (have rich EF experience too for comparison) and love how many possibilities you have to tune your code. Lazy loading, batch queries, read-only sessions etc.

    Cheers Vincent, keep blogging!

  2. > Unfortunatly, using multiple FetchMany statements is not allowed

    For good reason, as that causes a cartesian product. In other words: (number of projects) * (number of roles). Previous versions of NHibernate would allow this, so you’d end up with multiple objects representing the same entity.

    A different way to resolve the problem would be to use the “BatchSize” option. It is an global option though, so you need to be careful not to fix a local problem with a global solution. This strategy is best for value-object like entities which are cached anyway and are often queried throughout the entire application.

Leave a Reply

Your email address will not be published.