Optimize NHibernate Query

Question

Hi

In my system I do a centralized calculation on an aggregate with a lot of collections. I need ALL of the collections to be loaded before the calculation, and therefore I use a multicriteria that joins the collections on the root.

The criteria i listed here below. It takes approx 500ms to run on my local setup, and that is a lot of wait for the user. Does anybody have any ideas of how I could optimize this? Any help that can make this query faster is appreciated!

(I have changed the names of the collections for a little privacy :))

IMultiCriteria criteria = session.CreateMultiCriteria()
.Add(DetachedCriteria.For<Building>()
     .Add(Restrictions.Eq("Id", BuildingId))
     .CreateCriteria("ACollection", JoinType.LeftOuterJoin)
.Add(DetachedCriteria.For<Building>()
     .Add(Restrictions.Eq("Id", BuildingId))
     .CreateCriteria("BCollection", JoinType.LeftOuterJoin)
.Add(DetachedCriteria.For<Building>()
     .Add(Restrictions.Eq("Id", BuildingId))
     .CreateCriteria("CCollection", JoinType.LeftOuterJoin)
.Add(DetachedCriteria.For<Building>()
     .Add(Restrictions.Eq("Id", BuildingId))
     .CreateCriteria("ECollection", JoinType.LeftOuterJoin))
.Add(DetachedCriteria.For<Building>()
     .Add(Restrictions.Eq("Id", BuildingId))
     .CreateCriteria("FCollection", JoinType.LeftOuterJoin))
.Add(DetachedCriteria.For<Building>()
     .Add(Restrictions.Eq("Id", BuildingId))
     .CreateCriteria("GCollection", JoinType.LeftOuterJoin))
.Add(DetachedCriteria.For<Building>()
     .Add(Restrictions.Eq("Id", BuildingId))
     .CreateCriteria("HCollection", JoinType.LeftOuterJoin))
.Add(DetachedCriteria.For<Building>()
     .Add(Restrictions.Eq("Id", BuildingId))
     .CreateCriteria("JCollection", JoinType.LeftOuterJoin))
.Add(DetachedCriteria.For<Building>()
     .Add(Restrictions.Eq("Id", BuildingId))
     .CreateCriteria("KCollection", JoinType.LeftOuterJoin))
.Add(DetachedCriteria.For<Building>()
     .Add(Restrictions.Eq("Id", BuildingId))
     .CreateCriteria("LCollection", JoinType.LeftOuterJoin))
.Add(DetachedCriteria.For<Building>()
     .Add(Restrictions.Eq("Id", BuildingId))
     .CreateCriteria("MCollection", JoinType.LeftOuterJoin))
.Add(DetachedCriteria.For<Building>()
     .Add(Restrictions.Eq("Id", BuildingId))
     .CreateCriteria("NCollection", JoinType.LeftOuterJoin))
.Add(DetachedCriteria.For<Building>()
     .Add(Restrictions.Eq("Id", BuildingId))
     .CreateCriteria("OCollection", JoinType.LeftOuterJoin))
.Add(DetachedCriteria.For<Building>()
     .Add(Restrictions.Eq("Id", BuildingId))
     .CreateCriteria("PCollection", JoinType.LeftOuterJoin))
.Add(DetachedCriteria.For<Building>()
     .Add(Restrictions.Eq("Id", BuildingId))
     .CreateCriteria("QCollection", JoinType.LeftOuterJoin))
.Add(DetachedCriteria.For<Building>()
     .Add(Restrictions.Eq("Id", BuildingId))
     .CreateCriteria("RCollection", JoinType.LeftOuterJoin))
.Add(DetachedCriteria.For<Building>()
     .Add(Restrictions.Eq("Id", BuildingId))
     .CreateCriteria("SCollection", JoinType.LeftOuterJoin))
.Add(DetachedCriteria.For<Building>()
     .Add(Restrictions.Eq("Id", BuildingId))
     .CreateCriteria("TCollection", JoinType.LeftOuterJoin))
.Add(DetachedCriteria.For<Building>()
     .Add(Restrictions.Eq("Id", BuildingId))
     .CreateCriteria("UCollection", JoinType.LeftOuterJoin))
.Add(DetachedCriteria.For<Building>()
     .Add(Restrictions.Eq("Id", BuildingId))
     .CreateCriteria("VCollection", JoinType.LeftOuterJoin))
.Add(DetachedCriteria.For<Building>()
     .Add(Restrictions.Eq("Id", BuildingId))
     .CreateCriteria("WCollection", JoinType.LeftOuterJoin))
.Add(DetachedCriteria.For<Building>()
     .Add(Restrictions.Eq("Id", BuildingId))
     .CreateCriteria("XCollection", JoinType.LeftOuterJoin))
.Add(DetachedCriteria.For<Building>()
     .Add(Restrictions.Eq("Id", BuildingId))
     .CreateCriteria("YCollection", JoinType.LeftOuterJoin))
.Add(DetachedCriteria.For<Building>()
     .Add(Restrictions.Eq("Id", BuildingId))
     .CreateCriteria("ZCollection", JoinType.LeftOuterJoin))
.Add(DetachedCriteria.For<Building>()
     .Add(Restrictions.Eq("Id", BuildingId))
     .CreateCriteria("AACollection", JoinType.LeftOuterJoin))
.Add(DetachedCriteria.For<Building>()
     .Add(Restrictions.Eq("Id", BuildingId))
     .CreateCriteria("ABCollection", JoinType.LeftOuterJoin))
.Add(DetachedCriteria.For<Building>()
     .Add(Restrictions.Eq("Id", BuildingId))
     .CreateCriteria("ACCollection", JoinType.LeftOuterJoin))
.Add(DetachedCriteria.For<Building>()
     .Add(Restrictions.Eq("Id", BuildingId))
     .CreateCriteria("ADCollection", JoinType.LeftOuterJoin))
.Add(DetachedCriteria.For<Building>()
     .Add(Restrictions.Eq("Id", BuildingId))
     .CreateCriteria("AECollection", JoinType.LeftOuterJoin));

Answer 1

The very first thing I could recommend would be to fire up your Sql Profiler and get an exact copy of the sql coming in. Then I would take that and put it in the Sql Tuner, this will most likely recommend creating new indexes and adding/updating statistics on some or all of the tables.

At this point I would then run the performance testing before diving into trying to performance tune the HQL.

Answer 2

After a while I have finally realized, that for this exact scenario it might make a lot more sense to use a document-oriented database like MongoDB or an object database.

That way I can load the entire aggregate in one go and forget about joins.

So for anyone who runs into scenarios like the above, consider using an document-oriented approach.

My initial tests shows promise :)

One introduction to MongoDB can be found here: http://mookid.dk/oncode/archives/1057