In this recipe, we eagerly load the ActorRoles for our Movies to avoid a select N+1 bug. In a select N+1 situation, with one select statement, you load some entities from the database and begin to enumerate through them. As you enumerate through them, you access some lazy loaded property or collection. This triggers a separate database query for each entity in the original query. If we iterated through 1000 entities, we would have the original query plus 1000 nearly identical queries because we triggered lazy loading, hence the name select N+1. This creates N+1 round trips to the database, which will quickly kill performance, overwork the database, and could even crash the database.

In this recipe's code, we iterate through each product in the database. For each movie, we display the name of the actor in the starring role. This would normally trigger a separate database query for each movie, a potential select N+1 problem.

Our recipe uses two Futures queries. The first simply returns all products, sorted by unit price. The second Futures query, shown next, has the secret sauce:

_session.CreateQuery(
  @"from Movie m
    left join fetch m.Actors")
  .Future<Movie>();

We query for all movies with a left outer join to actor roles. This returns all movies and their actor roles, including the movies without actor roles. The word fetch tells NHibernate that we want to also load the actor role entities, not just join for the purposes of filtering. So, our second Futures query loads all Movies and their ActorRoles.

You have probably noticed that we don't actually assign the resulting IEnumerable<Movie> to a variable and use it anywhere. That's because we don't actually care about the results of this query. Its only purpose is to sneak into the session's hidden MultiQuery for futures and get executed.

When we enumerate the result of the first query, both queries get executed. First, NHibernate loads up all the products, including movies, and puts those in the session cache. At this point, each movie's Actors collection is uninitialized. When NHibernate executes the second query, it initializes those collections as it loads the query results.

The end result is that we can output the name of the movie's star without causing another query. That data has already been loaded.

The following are the resulting SQL queries:

select   product0_.Id          as Id1_,
         product0_.Version     as Version1_,
         product0_.Name        as Name1_,
         product0_.Description as Descript5_1_,
         product0_.UnitPrice   as UnitPrice1_,
         product0_.Director    as Director1_,
         product0_.Author      as Author1_,
         product0_.ISBN        as ISBN1_,
         product0_.ProductType as ProductT2_1_
from     Product product0_
order by product0_.UnitPrice asc

select movie0_.Id          as Id1_0_,
       actors1_.Id         as Id0_1_,
       movie0_.Version     as Version1_0_,
       movie0_.Name        as Name1_0_,
       movie0_.Description as Descript5_1_0_,
       movie0_.UnitPrice   as UnitPrice1_0_,
       movie0_.Director    as Director1_0_,
       actors1_.Version    as Version0_1_,
       actors1_.Actor      as Actor0_1_,
       actors1_.Role       as Role0_1_,
       actors1_.MovieId    as MovieId0__,
       actors1_.Id         as Id0__,
       actors1_.ActorIndex as ActorIndex0__
from   Product movie0_
       left outer join ActorRole actors1_
         on movie0_.Id = actors1_.MovieId
where  movie0_.ProductType = 'Eg.Core.Movie'

An alternative for greatly reducing the impact of a select N+1 problem is to use the batch-size property in the mapping. Suppose we had added batch-size to our movies mapping, as shown in the following code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
    assembly="Eg.Core"
    namespace="Eg.Core">
  <subclass name="Movie" extends="Product">
    <property name="Director" />
    <list name="Actors" cascade="all-delete-orphan"
          batch-size="10">
      <key column="MovieId" />
      <index column="ActorIndex" />
      <one-to-many class="ActorRole"/>
    </list>
  </subclass>
</hibernate-mapping>

With a typical select N+1 bug, we would trigger a query on each movie. When we set batch-size to 10, this behavior changes. NHibernate needs to query for the contents of an Actors collection to initialize it, but it notices the batch-size setting. It finds nine other uninitialized Actors collections in the session and loads all of them at once with a single query.

If we have 10 movies, we only need two queries instead of 11. For 20 movies, we need three instead of 21 and so on. This cuts out about 90 percent of our queries.