Bullet, by Richard Huang, is a gem that can alert us in a variety of ways when our application performs an inefficient database query like an N+1 query or a missing counter cache column. In this episode we’ll use it to optimize a Rails application.

Optimizing Our Products Page

The page shown below lists products by the category they’re in. There are two models involved on this page: one is Category and this can have many Products.

This page currently suffers from the N+1 query problem and we can see this by looking at the Rails log. This shows that we perform one query to get the categories then a separate query for each category to fetch its products.

This is what the N+1 query problem is: making a query to fetch the parent then any number of child queries to fetch the other records. This kind of problem can be easy to overlook and this is where Bullet comes in useful. We’ll add it to our application’s gemfile, but only in the development group, then run bundle to install it.

/Gemfile

gem 'bullet', group::development

We’ll enable Bullet in a new initializer file. Since it won’t be loaded in every environment we first check to see that it’s defined. If it is we enable it and tell it how we want to be notified about query issues. We’ll set alert to true which will alert us through the browser.

/config/initializers/bullet.rb

ifdefined?BulletBullet.enable = trueBullet.alert = trueend

When we restart the server and reload the page we get a JavaScript alert telling us that Bullet has detected an N+1 query and showing what we should do to fix it.

We’ll follow Bullet’s recommendations and fetch the products at the same time we fetch the categories.

Now we fetch the products through eager loading since we need this data anyway. When we reload the page now we don’t get an alert as we’re fetching the data efficiently. If we look in the log file we’ll see that the data is fetched by only two queries, one to get the categories and one to get the products in those categories.

Bullet can also tell us when we’re doing eager loading unnecessarily. Let’s say that we decide to move the lists of products out of this index page and into the show page for each product. We’ll remove the code that lists the products in the index template so that we’re only displaying information about the categories.

Counter Cache Columns

Bullet will also notify us when we should consider using a counter cache column. Let’s say that under each of the category names we want to display the number of products in that category. We can do that like this:

This time it tells us that we should add a counter cache column. Our app needs to perform a database query for each category so that it can count its products. This is similar to the N+1 Query problem we had earlier. We can fix this easily by using the counter_cache option in the call to belongs_to in the Product model.

This will update the products count for the existing categories. We can now run these migrations with rake db:migrate and when we reload the page again the alert will be gone.

Other Notification Options

So far we’ve only seen one way that Bullet can notify us, through an alert message, but there are a number of options that we can choose to be notified. This is done through the Uniform Notifier gem which is an interesting project in itself. After we’ve tried Bullet out in our application we could switch the notification messages to something less intrusive, such as the bullet_logger. This way we can carry on developing our app and check this occasionally to see if there are any query issues.

One thing that’s important with any tool like this is that we don’t blindly follow its suggestions. If an alert message tells us to add eager loading we shouldn’t just add it to make the message go away. There are many times when adding eager loading can actually make performance worse so we should consider other optimizations such as caching. When it doubt it’s a good idea to use benchmarking to compare the performance of different solutions. We should also keep in mind that the environment that our production server is set up in can play a part as well, for example the latency of the database connections.