Eager loading aggregate with many child collections

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.

Posted Sat, 06 September 2008 08:05:00 AM by gabriel.schenker

Filed under: relation, lazy loading