I've said this before and I'll say it again: Lord Tunderin' Jayzus!

The target this time is NHibernate Profiler. As of this moment, I've had it "installed" for all of fifteen minutes and I've already reduced the query count of one page from 205 to 1. Such is the story you will read about today. I suspect typing it out will take longer than it took me to optimize the page I'm about to describe.

By the way, I put "installed" in quotes because installing the application consists of unzipping it to a folder of your choosing. Means you need to set up shortcuts on your own but I like the minimalist approach myself.

The app I've already described. To sum up, Surveyors have many MonumentAssignments. Here is the (original) Fluent NHibernate mapping for review:

public SurveyorMap() {
    WithTable("Surveyor");

    Id(x => x.ID, "SurveyorID")
        .WithUnsavedValue(0)
        .GeneratedBy.Identity();

    // Boring stuff

    HasMany( x => x.Monuments )
        .Cascade.AllDeleteOrphan( )
        .WithKeyColumn( "SurveyorID" );
}

Notice how I put (original) in brackets there. That's what we in the legitimate journalism field like to call "foreshadowing" (or, more likely, foreboding).

The page in question provides a list of Surveyors. First name, last name, commission number. That's it. It isn't displaying anything from the Monuments collection. (See? More foreshadowing.)

So it was much to my surprise when I profiled this application that it outlined a total of 205 SQL statements for 202 Surveyors. "Odd" says I, and I proceeded to work my way through NHibernate Profiler to see what it can tell me.image

Quite a bit as it turns out. The screenshot at right shows a summary of the alerts. The one that jumped out at me was 199 SELECT N+1 alerts.

The ellipsis took me to this page which was pretty helpful. It says if I'm going to iterate over the collection, I should consider eager loading. Well, you won't accuse me of not being eager so that's what I did. My GetAll method now looked like this:

public IList GetAll()
{
    var criteria = DetachedCriteria.For( );
    criteria.SetFetchMode( "Monuments", FetchMode.Eager );
    return criteria.GetExecutableCriteria( Session ).List( );
}

That baby reduced my query count from 205 to 3. And my alerts consisted of two "Use of implicit transactions discouraged" and one "Unbounded result set". "Acceptable" says I!

But then something occurred to me. I'm not using the Monuments collection in this page. Why is it even retrieving them? Maybe I *am* being too eager after all...

As it turns out, the HasMany call in my map doesn't retrieve the Monuments lazily by default as I had expected it would. So with a flourish of my hand, I remedy that:

public SurveyorMap() {
    WithTable("Surveyor");

    Id(x => x.ID, "SurveyorID")
        .WithUnsavedValue(0)
        .GeneratedBy.Identity();

    // Boring stuff

    HasMany( x => x.Monuments )
        .Cascade.AllDeleteOrphan( )
        .WithKeyColumn( "SurveyorID" )
        .LazyLoad();
}

I also reverted my previous eager change because it was no longer necessary and poof! I am now down to a single SQL query for the page, which is as it should be. I still prefers that I limit my result set and put things in an explicit transaction so I'll be tackling those shortly. But for now, I'm agog, AGOG I TELL YOU!!1!

Granted, exposing my ignorance is not exactly an Olympic event. But just this morning, I made some facetious remark (no, it's true) that I wanted to make an application that was wildly successful in spite of itself. Several people responded with variations of "is there any other kind?" Given what I know of its creator, I'm hoping this is an exception: an application that is wildly successful because it fills a niche *and* because it is reliable and well-built.

That said, Ayende, if you're reading, it'd be nice if you could clear individual sessions from the list...

Kyle the Reduced