Tools to help you detect n+1 queries

This is a little reference of tools to help you detect n+1 queries on a rails app.

Bullet
Prosopite
n plus one control
strict loading
Rack mini profiler

Bullet

How can it help you?

It will watch your queries while you develop your application and notify you when it detects a problem.

It can detect:

n+1 queries
eager-loaded associations which are not used
unnecessary COUNT queries which could be avoided with a counter cache

You can use it on the development and testing environments.

Sometimes Bullet may notify you of query problems you don’t care to fix, or which come from outside your code. You can whitelist these to ignore them.

How does the error report looks like?

For n+1 queries…

GET /posts
USE eager loading detected
  Post => [:comments]
  Add to your query: .includes([:comments])
Call stack
  /Users/benitoserna/code/bullet-test/app/views/posts/index.html.erb:20:in `map'
  /Users/benitoserna/code/bullet-test/app/views/posts/index.html.erb:20:in `block in _app_views_posts_index_html_erb__1178069968615334744_70147771830640'
  /Users/benitoserna/code/bullet-test/app/views/posts/index.html.erb:16:in `_app_views_posts_index_html_erb__1178069968615334744_70147771830640'

For unused eager loading…

GET /posts
AVOID eager loading detected
  Post => [:comments]
  Remove from your query: .includes([:comments])
Call stack

How do you use it?

Add it to your application’s Gemfile and run bundle install:

group :development, :test do
  gem "bullet"
end

Enable the Bullet gem with the command

bin/rails g bullet:install

This will help you configure bullet on the development and test enviroments.

References

github/flyerhzm/bullet

Prosopite

How can it help you?

Like Bullet, Prosopite is able to auto-detect Rails N+1 queries, but it also can help you detect some cases where bullet will give you false positives or false negatives.

Prosopite monitors all SQL queries using the Active Support instrumentation and looks for a pattern which is present in all N+1 query cases: More than one queries have the same call stack and the same query fingerprint.

Compared to bullet, Prosopite can auto-detect the following extra cases of N+1 queries:

N+1 queries after record creations (usually in tests)

FactoryBot.create_list(:leg, 10)

Leg.last(10).each do |l|
  l.chair
end

Not triggered by ActiveRecord associations

Leg.last(4).each do |l|
  Chair.find(l.chair_id)
end

First/last/pluck of collection associations

Chair.last(20).each do |c|
  c.legs.first
  c.legs.last
  c.legs.pluck(:id)
end

Changing the ActiveRecord class with #becomes

Chair.last(20).map{ |c| c.becomes(ArmChair) }.each do |ac|
  ac.legs.map(&:id)
end

Mongoid models calling ActiveRecord

class Leg::Design
  include Mongoid::Document
  ...
  field :cid, as: :chair_id, type: Integer
  ...
  def chair
    @chair ||= Chair.where(id: chair_id).first!
  end
end

Leg::Design.last(20) do |l|
  l.chair
end

How does the error report looks like?

The report will show you the N+1 queries detected and the call stack.

N+1 queries detected:
  SELECT `users`.* FROM `users` WHERE `users`.`id` = 20 LIMIT 1
  SELECT `users`.* FROM `users` WHERE `users`.`id` = 21 LIMIT 1
  SELECT `users`.* FROM `users` WHERE `users`.`id` = 22 LIMIT 1
  SELECT `users`.* FROM `users` WHERE `users`.`id` = 23 LIMIT 1
  SELECT `users`.* FROM `users` WHERE `users`.`id` = 24 LIMIT 1
Call stack:
  app/controllers/thank_you_controller.rb:4:in `block in index'
  app/controllers/thank_you_controller.rb:3:in `each'
  app/controllers/thank_you_controller.rb:3:in `index':
  app/controllers/application_controller.rb:8:in `block in <class:ApplicationController>'

How do you use it?

Add this line to your application’s Gemfile:

gem 'prosopite'

And then execute:

$ bundle install

Development environment

Prosopite auto-detection can be enabled on all controllers with…

class ApplicationController < ActionController::Base
  unless Rails.env.production?
    before_action do
      Prosopite.scan
    end

    after_action do
      Prosopite.finish
    end
  end
end

And the preferred notification channel should be configured:

# config/environments/development.rb

config.after_initialize do
  Prosopite.rails_logger = true
end

Test environment

Tests with N+1 queries can be configured to fail with:

# config/environments/test.rb

config.after_initialize do
  Prosopite.rails_logger = true
  Prosopite.raise = true
end

And each test can be scanned with:

# spec/spec_helper.rb

config.before(:each) do
  Prosopite.scan
end

config.after(:each) do
  Prosopite.finish
end

References

github/charkost/prosopite

NPlusOneControl

How can it help you?

It gives you rspec and minitest matchers to prevent the n+1 queries problem.

It evaluates the code under consideration several times with different scale factors to make sure that the number of DB queries behaves as expected (i.e. O(1) instead of O(N)).

So, it’s for performance testing and not feature testing.

How does the error report looks like?

In the default mode it can give you something like this.

Expected to make the same number of queries, but got:
  10 for N=2
  11 for N=3
Unmatched query numbers by tables:
  resources (SELECT): 2 != 3
  permissions (SELECT): 4 != 6

And in the “verbose” mode, it can give you something like this…

Expected to make the same number of queries, but got:
  2 for N=2
  3 for N=3
Unmatched query numbers by tables:
  resources (SELECT): 2 != 3
Queries for N=2
   SELECT "resources".* FROM "resources" WHERE "resources"."deleted_at" IS NULL
   ↳ app/controllers/resources_controller.rb:32:in `index'
   ...
Queries for N=3
   ...

How do you install it?

Add it to your application’s Gemfile and run bundle install:

group :test do
  gem "n_plus_one_control"
end

How do you use it?

First, add NPlusOneControl to your spec_helper.rb:

require "n_plus_one_control/rspec"

Then:

# Wrap example into a context with :n_plus_one tag
context "N+1", :n_plus_one do
  # Define `populate` callbacks which is responsible for data
  # generation (and whatever else).
  #
  # It accepts one argument – the scale factor (read below)
  populate { |n| create_list(:post, n) }

  specify do
    expect { get :index }.to perform_constant_number_of_queries
  end
end

References

Rails strict_loading

How it can help you?

You can add #strict_loading! to any record to prevent lazy loading of associations. Strict will cascade down from the parent record to all the associations to help you catch any places where you may want to use preload instead of lazy loading.

How do you use it?

On a record…

user = User.first
user.strict_loading!
user.comments.to_a
=> ActiveRecord::StrictLoadingViolationError

On a relation…

user = User.strict_loading.first
user.comments.to_a
=> ActiveRecord::StrictLoadingViolationError

References

Rack mini profiler

How it can help you?

It can help you with more than just detecting n+1 queries!… It is a production and development profiler, it allows you to quickly isolate performance bottlenecks, both on the server and client.

It can help you with…

Database profiling
Call-stack profiling
Memory profiling

How does the report looks like?

It displays a speed badge for every html page that if you click it, it will show you a page with the profiling information of the current page.

miniprofiler

And if you click on of the sql queries count, it will show you a list with all the queries…

miniprofiler

Although it will not tell you exactly that you have an n+1 quieries problem, it can help you a lot to visualize it.

How do you use it?

On development

In rails all you have to do is to include the Gem and you’re good to go in development.

Install/add to Gemfile in Ruby 2.4+ (The gem is added by default to rails since version 6.1)

gem 'rack-mini-profiler'

NOTE: Be sure to require rack_mini_profiler below the pg and mysql gems in your Gemfile. rack_mini_profiler will identify these gems if they are loaded to insert instrumentation. If included too early no SQL will show up.

You can also include optional libraries to enable additional features.

# For memory profiling
gem 'memory_profiler'

# For call-stack profiling flamegraphs
gem 'stackprof'

On production

It is designed with production profiling in mind. To enable that run Rack::MiniProfiler.authorize_request once you know a request is allowed to profile.

# inside your ApplicationController

before_action do
  if current_user && current_user.is_admin?
    Rack::MiniProfiler.authorize_request
  end
end

References

Conclusion

As you can see, each tool can help you in different ways, some of the could be of more value to you than others, but that is not a problem, because you can use them in combination if you need it.

Do you want to solve n+1 queries with confidence?

Understand the methods (joins, includes, etc...) to work with associations.
Identify when active record will execute a query.
And the tools that can help you detect n+1 queries (like the bullet gem).

Tools to help you detect n+1 queries

Bullet

How can it help you?

How does the error report looks like?

How do you use it?

References

Prosopite

How can it help you?

How does the error report looks like?

How do you use it?

Development environment

Test environment

References

NPlusOneControl

How can it help you?

How does the error report looks like?

How do you install it?

How do you use it?

References

Rails strict_loading

How it can help you?

How do you use it?

References

Rack mini profiler

How it can help you?

How does the report looks like?

How do you use it?

On development

On production

References

Conclusion

Related articles

Do you want to solve n+1 queries with confidence?