Today I had to deal with optimizing a page. Under certain circumstances, it would take ~30 seconds to run. On my machine, of course, it would load nearly instantly. The first thing that I did was put a DB profiler and watch the traffic going to the DB server. In my experience, that should always be the first place to look.

No, not because I am using an OR/M, because that is usually the most common place for remote calls, and that is usually has an order of magnitude higher cost than anything else that I can do in the application. It also helps that openning a DB Profiler is as simple matter as it can be. Now, again, I could have looked for costly queries, but I didn't think that I would have much, I looked for lots of queries.

I found that the page used 32 separate queries to the database in order to get the data. That wasn't nice of this page, of course, but the depressing part was that it wasn't the usually "Select N+1 - slap an eager load on it and move on", it was a true case of a page that simply needed to display a wide object graph, which required a lot of queries to load. Frankly, lazy loading was just part of it, the other part of it was that the controller for this page had performed quite a few queries on its own (get me paged count of decendants, get me count of friends, etc).

So, I rolled up my sleeves and started to go over the controller, looking for all the places that it performed any kind of data access. After isolating those, I create a method "EagerLoadDataEfficently" and started to work. I am using NHibernate trunk, which means that I have the lovely Multi Criteria support, that made my life a lot easier.

What I basically had to do was to take each query in place, and stuff it into the Multi Criteria, execute that, and get on with it. It took about an hour and a half*, and was a lot of fun. End result, we got the page down to 4 queries, and that was because it had two <iframe>s in it, and I didn't feel like managing the state between three separate requests.

Zero code changes in the UI layer, zero model changes as a result. All the changes were in tuning the Multi Criteria. In this case, it really was a no-brainer optimization. I am very pleased with this feature. I am going to put a screen cast soon about what it can do, by the way.

* Had to split responsabilities between classes, I reused a controller where I shouldn't have, which caused some delay.