Ayende @ Rahien

My name is Oren Eini
Founder of Hibernating Rhinos LTD and RavenDB.
You can reach me by phone or email:


+972 52-548-6969

, @ Q c

Posts: 6,026 | Comments: 44,842

filter by tags archive

PerformanceMultiply Collections Fetch With NHibernate

time to read 12 min | 2310 words

We are back to the traditional model of Blog -> Posts -> Comments. Now, we want to load all the blogs, posts and comments and index them to improve search performance. A naive implemention will use this code:

foreach (Blog blog in session.CreateQuery("from Blog").List<Blog>())



       foreach (Post post in blog.Posts)


              Index(post, "Post");

              foreach (Comment comment in post.Comments)


                     Index(comment, "Comments");




This code is going to produce an ungodly amount of database queries. We want to grab the data from the database in as few queries as possible. Obviously we want to use some sort of eager loading, but what can we use? Let us analyze what are our options for solving this.

The easiest route is to tell NHibernate to load the entire object graph in one go. For a long time, NHibernate had a limitation of a single collection eager load per query. This means that you couldn't load both the Posts collection and the Comments collection. Sergey has recently removed this limitation, so let us replacing the HQL query with:

from Blog blog

     left join fetch blog.Posts post

     left join fetch post.Comments

This query results in this SQL (I cleaned up the names a bit):

select  blog.Id ,                                    post.BlogId ,

        post.Id ,                                    post.UserId ,

        comment.Id ,                                 comment.Name ,

        blog.Title ,                                 comment.Email ,

        blog.Subtitle ,                              comment.HomePage ,

        blog.AllowsComments ,                        comment.Ip ,

        blog.CreatedAt ,                             comment.Text ,

        post.Title ,                                 comment.PostId ,

        post.Text ,                                  post.BlogId ,

        post.PostedAt ,                              post.Id ,

        comment.PostId ,                             comment.Id

from    Blogs blog

        left outer join Posts post on blog.Id = post.BlogId

        left outer join Comments comment on post.Id = comment.PostId

A word of warning, though:

There is a non trivial cartesian product here. If you know that you are working on small sets, that is fine, but be wary of using this technique over large sets of data.

More posts in "Performance" series:

  1. (17 Mar 2007) Multiply Collections Fetch With NHibernate
  2. (23 Oct 2006) Make The Developers Feel The Pain



Wouldn't the following be just as efficient, without the chance of getting a huge Cartesian product?

IList list = (IList)session.CreateMutliQuery()

.Add("from Blog")

.Add("select p from Post p left join p.Blog b")

.Add("select c from Comment c left join c.Post p left join p.Blog b")


foreach (Blog blog in list)


I'm a bit uncertain if I used the right syntax, I mostly use the query API, and the point with the joins is so you can add a where statement to the blog, and simply add the same statement in the other 2 queries to avoid pulling out other posts/comments.


I've been waiting for this feature a long time! Perfect!

Can you please inform where to find this update?

Comment preview

Comments have been closed on this topic.


No future posts left, oh my!


  1. Technical observations from my wife (3):
    13 Nov 2015 - Production issues
  2. Production postmortem (13):
    13 Nov 2015 - The case of the “it is slow on that machine (only)”
  3. Speaking (5):
    09 Nov 2015 - Community talk in Kiev, Ukraine–What does it take to be a good developer
  4. Find the bug (5):
    11 Sep 2015 - The concurrent memory buster
  5. Buffer allocation strategies (3):
    09 Sep 2015 - Bad usage patterns
View all series


Main feed Feed Stats
Comments feed   Comments Feed Stats