Have Geek Brain

Optimize NHibernate query

On my system, I am doing a centralized calculation in conjunction with a large number of collections. I need all collections to be loaded before being evaluated, and so I am using a multi criterion that concatenates the collections at the root.

The criteria are listed below. It takes about 500ms for my local setup and it waits a lot for the user. Does anyone have any ideas on how I could optimize this? Any help that can make this request faster is appreciated!

(I 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));
0


a source to share


2 answers


The very first thing I could recommend is to run your Sql profiler and get an exact copy of the sql. Then I will 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 then ran performance testing before diving into HQL performance tuning.

+1


a source


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

This way I can load the entire collection in one go and forget about the joins.

So, for those who come across scenarios like the above, consider using a doc.



My initial tests show promise :)

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

+2


a source






More articles:


All Articles