Bullet: a gem to help reduce N+1 queries

Lucía Escanellas
November 11, 2010 | 3 min read

Bullet is a plugin written by Richard Huang, that helps reducing the number of queries an application makes. It was first posted on 2009, but it is still a pretty useful gem to monitor your application for performance improvements.
It has several ways of notifying problems: by Growl notifications, JavaScript alerts by default, and even using XMPP too. Additionally, it saves on its own bullet.log the exact line and stack trace of what caused the alert, and if you want to, it can also write to the application log.

The project is on GitHub: http://github.com/flyerhzm/bullet

You are probably familiar with the N+1 query and cache counter problems, so let’s look how Bullet will detect these by monitoring the database queries.

The N+1 query problem

Suppose we have our application with accounts having many rate plans:

If we iterate over the rate plans, accessing rate plans attributes, like:

Will be using N+1 queries:

  • one query to get all the rate plans
  • then, for each rate plan, one query to get the corresponding account.

(This means that if we have 1000 posts, we’ll be doing 1000+1 queries)

By using the :include option, we can retrieve the rate plans and their corresponding accounts on the same query:

By detecting if we forgot to use eager loading or a counter cache, Bullet can save time looking for problems on the fly.
For example, Bullet will show this message:

N+1 Query Detected Message

We have to be careful not to abuse the :include option. If the rate_plans table is big enough, it could take too much server memory. Also, we could end up with big, slow requests, or getting from the server a lot of data that won’t be used. Bullet will show a message if it detects an unused eager loading.

Cache counters

Cache counters is another improvement in a one-to-many relationship. Because the _has_many_ relationship defines an attribute that is a collection, if we use the size property on this attribute, it will trigger a select count(*) on the child table. This is generally acceptable, except when we are using frequently count and we end up going to the database unnecessarily.
In order to avoid that, we can use counter caching: Active Record will maintain for a parent table the number of child references. Bullet also shows a message if it finds a cache counter is needed.


You have to first install the gem, by adding it to the Gemfile, or by installing manually:

The next thing is to add some configuration to the development environment. Bullet won’t do anything unless you configure it explicitly, and also note that it’s not a good idea to configure it on the production environment, because on that case your users would also receive those N+1 query alerts.

Configuration allows Growl notifications, and sending messages by XMPP (Jabber), but since the default JavaScript alerts are good enough for me, I configured it like this:

Note the last option, because disabling browser cache can save you some trouble on some configurations. In any case, if Bullet is not working, try first disabling browser cache.

With this configuration, it will show a JavaScript alert, the same message on the console, and the detail and stack trace on the bullet.log.

Get our stories delivered to your inbox weekly.