Case Sensitive Optimization

time to read 2 min | 335 words

I am doing some optimization work lately, and I managed to take a page down to three DB requests per page load, but I couldn't get it to less than that, and it really annoyed me. The problem was that accessing a certain properly on an entity would cause a lazy load, even though I have explicitly told it to eagerly load that.

That was... annoying.

Eventually I got rid of all the "it can't be!" mentality and looked at what was going on, the problem was that I was looking for NHibernate bug. The problem was actually more subtle than that. Let us say that I have this scenario:

Post post = session
  .Add(Expression.Eq("Id", 15))
  .SetFetchMode("Status", FetchMode.Join)

Console.WriteLine( post.Status.Name );// should not cause lazy loading.

I looked at the result, and looked at the result, and couldn't quite figure it out. Eventually it dawned upon me that the Posts' table had the following data:

Id Content Status
15 Interesting... PUBLISHED

While the PostStatuses table had this data:

Id Name
Published Published

Do you see the problem? While NHibernate has correctly loaded the associated PostStatus entity, it has loaded it with the 'Published key, while the PostStatus entity on the Post was loaded with the 'PUBLISHED' key. Hence, it didn't exists in the current session, and when accessed, would cause a lazy load. Of course, at the DB level it didn't matter because it is set up to be case insensitive, but it sure made a different for comparing key equality on the CLR.

In this case, it was a simple case of entering the wrong value to the PostStatuses table that cause all this issue. As an aside, NHibernate has ways to deal with such cases in legacy databases, but that is something that I would rather fix in the data layer.