NHibernate Forge
The official new home for the NHibernate for .NET community

Eager loading aggregate with many child collections

Blog Signature Gabriel

In a comment to my post on lazy loading versus eager loading which you can find here I have been asked whether NHibernate 2.0 can now eagerly load an aggregate where the root has more than one child collection. The answer is yes but... Let's discuss it with a sample.

Imagine having the following (academic) domain model.

I have identified a Blog-aggregate. This aggregate has the Blog entity as root and has two child collections Posts and Readers. Additionally it has an Author child. If I want to eager load this aggregate with NHibernate I have several possibilities to do so. (If you are new to NHibernate please refer to this post for a detailed introduction.)

Preparing the initial data

For my tests I need some data in my sample database. I generate a single blog with one author, 3 readers and two posts as follows

private void CreateInitialData()
{
    var author = new Person {Name = "Gabriel Schenker"};
    _blog = new Blog {Name = "NHibernate FAQ", Author = author};
    var readers = new[]
                      {
                          new Person {Name = "John Doe"},
                          new Person {Name = "Sue Foo"},
                          new Person {Name = "Ann Li"},
                      };
    var posts = new[]
                    {
                        new Post {Title = "Lazy Loading - eager loading"},
                        new Post {Title = "MultiCriteria queries"},
                    };
    _blog.Readers.AddAll(readers);
    _blog.Posts.AddAll(posts);
 
    using (ISession session = SessionFactory.OpenSession())
    using (ITransaction transaction = session.BeginTransaction())
    {
        session.Save(author);
        session.Save(_blog);
        transaction.Commit();
    }
}

Not recommended solution

Let's start with the "bad" solution for the problem. I have defined the following test method

[Test]
public void Eager_load_blog_aggregate_in_NOT_recommended_way()
{
    Blog fromDb;
    using (ISession session = SessionFactory.OpenSession())
    {
        string sql = "from Blog b" +
                     " inner join fetch b.Author" +
                     " left join fetch b.Readers" +
                     " left join fetch b.Posts" +
                     " where b.Id=:id";
        fromDb = session.CreateQuery(sql)
            .SetGuid("id", _blog.Id)
            .UniqueResult<Blog>();
    }
 
    Assert.IsTrue(NHibernateUtil.IsInitialized(fromDb.Author));
    Assert.IsTrue(NHibernateUtil.IsInitialized(fromDb.Readers));
    Assert.IsTrue(NHibernateUtil.IsInitialized(fromDb.Posts));
    Assert.AreEqual(3, fromDb.Readers.Count);
    Assert.AreEqual(2, fromDb.Posts.Count);
}

This works, that is my test is green. Work done, I can go home - or can't I?

No, the fact that the test passes does not mean, that this is a GOOD solution! Let's analyze what SQL NHibernate generates. It is similar to that:

select          blog.Id as Id1_0_, 
                author.Id as Id2_1_, 
                readers.Id as Id2_2_, 
                posts.Id as Id3_3_, 
                blog.Name as Name1_0_, 
                blog.Author as Author1_0_, 
                author.Name as Name2_1_, 
                readers.Name as Name2_2_, 
                posts.Title as Title3_3_, 
                readers.BlogId as BlogId0__, 
                readers.Id as Id0__, 
                posts.BlogId as BlogId1__, 
                posts.Id as Id1__ 
from            Blog blog 
inner join      Person author on blog.Author=author.Id 
left outer join Person readers on blog.Id=readers.BlogId 
left outer join Post posts on blog.Id=posts.BlogId 
where           blog.Id='090de8b6-4c36-4f73-9d63-12dbd6b332b2'

Please notice that you have three joins, an inner and two outer joins. NHibernate returns a single set of records containing the cross product of all three joins. That is: if a blog has one author, 10 posts and 100 readers the result set will have 1*10*100 = 1000 records. If you have 100 posts and 1000 readers you will get a set with 100'000 records!

Now imagine the blog having three child collections instead of 2... the situation would be catastrophic and your DBA would insist that you get fired.

Is there a better way? Of course! NHibernate 2.0 introduces the IMultiQuery and IMultiCriteria interfaces. With these interfaces you can send several select statements to the database in one go (Note: not all databases support this, check your provider)

Better solution

HQL Multi Query

Let's start with the MultiQuery class. I define a test method as follows

[Test]
public void Eager_load_blog_aggregate_with_multi_query()
{
    Blog fromDb;
    using (ISession session = SessionFactory.OpenSession())
    {
        var sql1 = "from Blog b inner join fetch b.Author where b.Id=:id";
        var sql2 = "from Blog b inner join fetch b.Readers where b.Id=:id";
        var sql3 = "from Blog b inner join fetch b.Posts where b.Id=:id";
 
        var result = session.CreateMultiQuery()
                        .Add(session.CreateQuery(sql1))
                        .Add(session.CreateQuery(sql2))
                        .Add(session.CreateQuery(sql3))
                        .SetParameter("id", _blog.Id)
                        .List();
        IList list = (IList) result[0];
        fromDb = list[0] as Blog;
    }
 
    Assert.AreNotSame(_blog, fromDb);
    Assert.IsTrue(NHibernateUtil.IsInitialized(fromDb.Author));
    Assert.IsTrue(NHibernateUtil.IsInitialized(fromDb.Readers));
    Assert.IsTrue(NHibernateUtil.IsInitialized(fromDb.Posts));
    Assert.AreEqual(3, fromDb.Readers.Count);
    Assert.AreEqual(2, fromDb.Posts.Count);
}

NHibernate now sends 3 select statements in a single call to the database. I'm using an SQL Server 2005 database and the SQL sent by NHibernate is this

select  blog0_.Id as Id1_0_, 
        person1_.Id as Id2_1_, 
        blog0_.Name as Name1_0_, 
        blog0_.Author as Author1_0_, 
        person1_.Name as Name2_1_ 
from    Blog blog0_ 
inner join Person person1_ on blog0_.Author=person1_.Id 
where (blog0_.Id=@p0 );
 
select  blog0_.Id as Id1_0_, 
        readers1_.Id as Id2_1_, 
        blog0_.Name as Name1_0_, 
        blog0_.Author as Author1_0_, 
        readers1_.Name as Name2_1_, 
        readers1_.BlogId as BlogId0__, 
        readers1_.Id as Id0__ 
from    Blog blog0_ 
inner join Person readers1_ on blog0_.Id=readers1_.BlogId 
where (blog0_.Id=@p1 );
 
select  blog0_.Id as Id1_0_, 
        posts1_.Id as Id3_1_, 
        blog0_.Name as Name1_0_, 
        blog0_.Author as Author1_0_, 
        posts1_.Title as Title3_1_, 
        posts1_.BlogId as BlogId0__, 
        posts1_.Id as Id0__ 
from    Blog blog0_ 
inner join Post posts1_ on blog0_.Id=posts1_.BlogId 
where (blog0_.Id=@p2 );
 
; @p0 = '8558ccf4-449f-497a-8619-12458a0aa402', 
@p1 = '8558ccf4-449f-497a-8619-12458a0aa402', 
@p2 = '8558ccf4-449f-497a-8619-12458a0aa402'

SQL server will return 3 sets of records having 1, 3 and 2 records each (in our case). If I now assume I have a blog with 1 author, 10 posts and 100 readers SQL server will return 1+10+100=111 records. That's significantly less than in the bad sample from above where the database returns 1000 records. The situation is even better for the situation where I have 100 posts and 1000 reader. Here the database will return only 1+100+1000 = 1101 records. Compare this to the 100'000 records of the bad sample!

Multi Criteria Query

You can get the same result when using Criteria Queries. Take a look at the following test method

[Test]
public void Eager_load_blog_aggregate_with_multi_criteria()
{
    Blog fromDb;
    using (ISession session = SessionFactory.OpenSession())
    {
        var criteria1 = DetachedCriteria.For<Blog>()
            .Add(Restrictions.Eq("Id", _blog.Id))
            .SetFetchMode("Author", FetchMode.Eager)
            .CreateCriteria("Readers", JoinType.LeftOuterJoin);
        var criteria2 = DetachedCriteria.For<Blog>()
            .Add(Restrictions.Eq("Id", _blog.Id))
            .CreateCriteria("Posts", JoinType.LeftOuterJoin);
 
        var result = session.CreateMultiCriteria()
            .Add(criteria1)
            .Add(criteria2)
            .List();
        IList list = (IList) result[0];
        fromDb = list[0] as Blog;
    }
 
    Assert.AreNotSame(_blog, fromDb);
    Assert.IsTrue(NHibernateUtil.IsInitialized(fromDb.Author));
    Assert.IsTrue(NHibernateUtil.IsInitialized(fromDb.Readers));
    Assert.IsTrue(NHibernateUtil.IsInitialized(fromDb.Posts));
    Assert.AreEqual(3, fromDb.Readers.Count);
    Assert.AreEqual(2, fromDb.Posts.Count);
}

Here I only have defined 2 criteria since the blog has exactly one author I condensed the join with author and the join with readers. The resulting SQL is for my SQL server 2005

SELECT this_.Id as Id1_2_, 
this_.Name as Name1_2_, 
this_.Author as Author1_2_, 
person3_.Id as Id2_0_, 
person3_.Name as Name2_0_, 
person1_.BlogId as BlogId4_, 
person1_.Id as Id4_, 
person1_.Id as Id2_1_, 
person1_.Name as Name2_1_ 
FROM Blog this_ 
left outer join Person person3_ on this_.Author=person3_.Id 
left outer join Person person1_ on this_.Id=person1_.BlogId 
WHERE this_.Id = @p0;
 
SELECT this_.Id as Id1_1_, this_.Name as Name1_1_, 
this_.Author as Author1_1_, 
post1_.BlogId as BlogId3_, 
post1_.Id as Id3_, 
post1_.Id as Id3_0_, 
post1_.Title as Title3_0_ 
FROM Blog this_ 
left outer join Post post1_ on this_.Id=post1_.BlogId 
WHERE this_.Id = @p1;
 
; @p0 = 'dc29a877-3b28-4c3a-b8dd-4e015d77851d', 
@p1 = 'dc29a877-3b28-4c3a-b8dd-4e015d77851d'

Summary

I have shown you several ways how to eagerly load an aggregate where the root is having more than one child collection. I also have shown the pitfalls of a possible (bad) solution.

Blog Signature Gabriel .


Posted sep 06 2008, 06:05 p.m. by gabriel.schenker
Filed under: ,

Comments

mhnyborg wrote re: Eager loading aggregate with many child collections
on 09-06-2008 19:02

Is this possible with LINQ?

santo2 wrote re: Eager loading aggregate with many child collections
on 04-21-2009 11:34

I've got 4 one to many bag properties and 10 many to one properties. the many to one properties are not lazy. with the multicriteria that results to loading those 10 many to one properties 4 times, which is taking too long.

Is there another solution except lazy load the many to one properties?

santo2 wrote re: Eager loading aggregate with many child collections
on 05-04-2009 3:34

how do i make sure the lazy loading on a collection of one of the collections is false?

greets

gabriel.schenker wrote re: Eager loading aggregate with many child collections
on 05-05-2009 2:25

@santo 2: you might have a look at e.g. this post: nhforge.org/.../nhibernate-mapping-lt-set-gt.aspx

k03123 wrote re: Eager loading aggregate with many child collections
on 05-05-2009 23:54

in the example above lets say Post has Comments.

is it still possible to eagerly load Blog with all the Posts and Comments?

thanks

gabriel.schenker wrote re: Eager loading aggregate with many child collections
on 05-06-2009 2:53

@k03123 In general I would avoid such queries... they don't scale. Perhaps you can just show the number of comments per post and only retrieve the comments when the user drills down to an individual post.

k03123 wrote re: Eager loading aggregate with many child collections
on 05-06-2009 3:31

hi gabriel,

thanks for taking time to reply.

i was taking Blog, Posts and Comments as an example only. in my case i have a Report object which is the root and it has Groups which in turn has Regions. while displaying the Report, i need all the Regions to perform some calculation.

btw, somewhere on the internet i found that u can do that as:

from Blog b inner join fetch b.Posts p

inner join fetch p.Comments c

but this returns cartesan products and u need to use Set instead of bag for collection. so i was wondering if it is possible to do it using multiquery/multicriteria.

thanks

https://www.diigo.com/item/note/4u8c0/eu3m wrote https://www.diigo.com/item/note/4u8c0/eu3m
on 09-11-2014 19:52

Eager loading aggregate with many child collections - NHibernate blog - NHibernate Forge

Powered by Community Server (Commercial Edition), by Telligent Systems