codelevy

Fewer Queries != Faster

Posted by Lance Ivy Fri, 26 Oct 2007 15:53:00 GMT

Maybe it’s my complete lack of experience as a DBA, but I’ve never understood the need to make every report happen in just one query. I guess the theory is that as long as you get all the joins right, one query is better than three, right? The database is always faster?

For Example: Some Report I Already Forgot

Back when I had a regular day job, I was called over to help improve the performance of some financial report. Now I’m going to describe this with all kinds of fuzzy vagueness, cuz I’ve already forgotten any details. When I arrived, my fellow dev was trying to optimize a join over a couple tables that simultaneously did all the calculations he needed for the report. We struggled with that for a bit, then took a step back and wrote a simple finder followed by a loop to categorize and calculate the results. Voila, performance was now negligible and as a bonus, the routine was readable.

(Best example ever. I love how specific this one turned out.)

For Example: (Over-)Eager Loading

Ok, how about a more recent and practical example? In my recent profiling, I ran into a case where the program needed one record from each of three tables. Pretty easy, yeah? And one query is faster than three? Sounds like a case for eager loading.

Model.find_by_name(name, :include => [:singular_assoc, :other_singular_assoc])

Oops. Now ActiveRecord is generating a three-way join over moderately large tables. Turns out that was maybe some over-eager optimization. Benchmarks showed that three separate queries performed better by a factor of ten.

m = Model.find_by_name(name)
m.singular_assoc
m.other_singular_assoc

And So?

Is one query faster than three? Sometimes, maybe. Not always. Certainly not often enough to warrant blindly creating and maintaining needlessly complex SQL without considering other options. Sometimes it just makes more sense to break the query apart.