Combating the evil queries since... the day before yesterday

time to read 2 min | 309 words

Okay, you probably noticed by now that I'm talking a lot about the amount of queries that a page makes. Right now I managed to drop it from 91 to 72 (didn't test in the process, and wasted tons of time) from 72 to 63 (tested in the process, took minutes). It was all about putting lazy in the right places (and fixing the tests that assumed that the data is always there).

I then tried to find out exactly where most of the traffic was, the first part was an easy target, just finding out that I made a couple of dozen calls for the exact same data was a big relief. I used the ASP.Net Cache, and that dropped the number of queries to 41.

Then I was stumped. I knew what was going on, but I didn't know how to fix it. I'd the following problem: Blog->Posts->Comments. Where I need to do some processing on blogs that I thought was also involving the comments. The result of that was two statements to load the blog and the posts, and another 38 statements to load each post comments. Looking at the code, I realized that I didn't really need the comments for that particular process. (I likely will never need them for a blog-wide process). Marking the Comments as lazy dropped the number of queries to three. If I was really desperate, I could have gone to two (which is minimal amount of statements that I could produce manually). I don't choose to do that because… well, that would mean that the UI would have to make decisions about how the data would be loaded.

I rather leave that decision outside of the UI layer at the moment. It's not a problem to add this feature, but the difference between two or three queries is not big enough to justify adding responsibilities to the UI that don't belong there.