CODEWITHSUNDEEP
c#.netsql-servernhibernateorm
asgerhallas
asgerhallas

Reputation: 17714

Optimize NHibernate Query

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));

Upvotes: 0

Views: 1607

Answers (2)

asgerhallas
asgerhallas

Reputation: 17714

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

Upvotes: 2

Chris Marisic
Chris Marisic

Reputation: 33098

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.

Upvotes: 1

Related Questions