#372 Bullet

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.

Category Load (0.2ms)  SELECT "categories".* FROM "categories" ORDER BY name
Product Load (0.2ms)  SELECT "products".* FROM "products" WHERE "products"."category_id" = 3
Product Load (0.2ms)  SELECT "products".* FROM "products" WHERE "products"."category_id" = 1
Product Load (0.2ms)  SELECT "products".* FROM "products" WHERE "products"."category_id" = 5
Product Load (0.2ms)  SELECT "products".* FROM "products" WHERE "products"."category_id" = 4
Product Load (0.3ms)  SELECT "products".* FROM "products" WHERE "products"."category_id" = 2

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.

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.

if defined? Bullet
  Bullet.enable = true
  Bullet.alert = true
end

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.

class CategoriesController < ApplicationController
  def index
    @categories = Category.order(:name).includes(:products)
  end
end

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.

Category Load (0.2ms)  SELECT "categories".* FROM "categories" ORDER BY name
Product Load (0.4ms)  SELECT "products".* FROM "products" WHERE "products"."category_id" IN (3, 1, 5, 4, 2)

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.

<h1>Categories</h1>

<% @categories.each do |category| %>
  <div class="category">
    <h2><%= link_to category.name, category %></h2>
  </div>
<% end %>

When we reload the page now we’ll get an alert again this time telling us that Bullet has detected unused eager loading.

We can remove the call to includes in the CategoriesController to fix this.

class CategoriesController < ApplicationController
  def index
    @categories = Category.order(:name).includes(:products)
  end
end

Reloading the page now no longer give us an alert.

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:

<h1>Categories</h1>

<% @categories.each do |category| %>
  <div class="category">
    <h2><%= link_to category.name, category %></h2>
    <%= pluralize category.products.size, "product" %>
  </div>
<% end %>

When we reload the page now we get an alert again.

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.

class Product < ActiveRecord::Base
  belongs_to :category, counter_cache: true
  attr_accessible :name, :price, :category_id
end

We need to generate a migration to add the counter column to the products table.

$ rails g migration add_products_count_to_categories products_count:integer

Before we run the migration we’ll change it so that the counter_cache column has a default value.

class AddProductsCountToCategories < ActiveRecord::Migration
  def change
    add_column :categories, :products_count, :integer, default: 0, null: false
  end
end

If we want this to apply to the existing Product records we’ll need to fill in that column and we’ll create a separate migration to do this.

$ rails g migration cache_product_count

In this migration we could use ActiveRecord to fill this column but we won’t do that here. Instead we’ll write some SQL code.

class CacheProductsCount < ActiveRecord::Migration
  def up
    execute "update categories set products_count=(select count(*) from products where category_id=categories.id)"
  end

  def down
  end
end

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.