Solving the N + 1 query problem with Query Diet

As developers we use O/R mappers to pretend our data is stored as objects instead of flat tables. Unfortunately that abstraction breaks down frequently. Or as Joel would say:

All non-trivial abstractions, to some degree, are leaky.

One particular leak of O/R mapping is the N + 1 query problem, where the most innocent code can flood your database with hundreds or thousands of queries.

N + 1 queries are easy to overlook during development because we are used to mediocre response time on our development machines with disabled caches and class reloading. Relief is promised by debug bars like Rack::Bug and rails-footnotes which include the number of triggered database queries in the HTML of every page coming out of your application. Unfortunately they also do a million other things we don't care about. We wanted something that would alert us of excessive queries, but stays out of our way otherwise.

Enter Query Diet.

Query Diet inserts a tiny, translucent box into the upper right corner of your screen, informing you about the number of queries triggered by the last request and the time spent waiting for the database.

This is Query Diet being happy about 3 requests taking 66ms:

Happy Query Diet

This is Query Diet being angry about 103 requests taking 164ms:

Angry Query Diet

We're also quiet satisfied with Query Diet's installation procedure, which goes like this:

  1. config.gem 'query_diet'

That's it. No second step required.

Check out Query Diet on github.

You can follow any response to this post through the Atom feed.

Avatar

Fri, 25 Jun 2010 16:23:00 GMT

by henning

Tags: