Ayende @ Rahien

It's a girl

Case Sensitive Optimization

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
  .CreateCriteria(typeof(Post))
  .Add(Expression.Eq("Id", 15))
  .SetFetchMode("Status", FetchMode.Join)
  .UniqueResult<Post>();

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
DRAFT Draft

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.

Comments

Haacked
06/25/2007 12:23 AM by
Haacked

Yet another reason I prefer surrogate keys for ids in databases. ;)

Frans Bouma
06/25/2007 07:41 AM by
Frans Bouma

Same here, string / date based PK fields are evil if you use them for things like descriptions. A customerID as a string... ok, but this example perfectly proofs that an ID should have been used: Your whole Status table is redundant. It looks like a normalized table but the attribute that identifies a status is identical to the name attribute :)

Ayende Rahien
06/25/2007 08:07 AM by
Ayende Rahien

Frans,

It is actually there for a good reason, because:

CUSTOMERISDOFUS key is shown as "Documentation Issue" to the user

Thomas Eyde
06/25/2007 08:25 AM by
Thomas Eyde

OT: Why is the type specified twice, and with different syntax?

First with: CreateCriteria(typeof(Post)),

Then with: UniqueResult()

Ayende Rahien
06/25/2007 08:28 AM by
Ayende Rahien

Because the last is a generic method that saves me a cast, it has no other meaning.

Comments have been closed on this topic.