OR/M and Performance
time to read 6 min | 1032 words

I spent most of the day going over an NHibernate application that was slow. The cause was most probably large number of queries for the database. I took my own advice and enabled performance logging for the application and let it run for a few days. When I read the logs today it looks like there were a number of pages where the number of queries was outside of any reasonable proportion.

When I started reading the code for this pages, I realized that the problem wasn't with mis-use of NHibernate, or rather, it wasn't specific for NHibernate. The most offending page had something like:

foreach(string idStr in claimsIds)
{
  int id = int.Parse(idStr);
  Claim claim = Context.Session.Load<Claim>(id);
  claim.Status = ddlClaimsStatus.Value;
  Context.Session.Save(claim);
}
Context.Session.Flush();

foreach(string idStr in paymentsIds)
{
  int id = int.Parse(idStr);
  Payment payment = Context.Session.Load<Payment>(id);
  payment.LastUpdateDate = DateTime.Today;
  Context.Session.Save(payment);
}
Context.Session.Flush();

I think that you can see why it is making a large number of calls to the database. This is one of the few cases that I would recommend the use of a stored procedure, since it meets my two criterias for a SP:

  • Zero business logic
  • Works on a large set of records

The second worst page had something like this:

foreach(DataTable dt in ds.Tables)
{
  foreach(DataRow subItem in dt.Rows)
  {
     Claim claim = Context.Session.Load<Claim>.Load(subItem["id"]);
     // do something with the claim
  }
}

Hm, now why would this produce large number of queries to the database, I wonder...

Only on the fifth page we managed to find a true SELECT N+1 issue, which was something like;

//User and Groups, this actually cause two database calls, one to load the user (which would probably be loaded
// the second to load its users
ICollection<Group> groups = Context.User.Groups;
foreach(Group group in groups)
{
  foreach(Section section in group.Sections) // database query
  {
    //do something with the sections
  }
}

In this case, the fix was to explictly load all the data in advance, like this:

// only one database query here, get all the groups for the user as well as their sections
ICollection<Group> groups = Context.Session.CreateCriteria(typeof(Group))
  .Add(Expression.Eq("User", Context.User))
  .SetFetchMode("Sections", FetchMode.Eager)
  .List<Group>();

foreach(Group group in groups)
{
  foreach(Section section in group.Sections) // database query
  {
    //do something with the sections
  }
}

That was a true two minutes fix, unlike the other four pages, which require at least a partial re-write.

Tweet Share Share 0 comments
Tags: