There are many tools that can help you detect n+1 queries in different ways.

This is a little reference of some of those tools:

• Strict loading
• Rack mini profiler
• Bullet
• Prosopite
• n plus one control

You don’t need to use all of them, but is good to know that they exists and how they can help you.

Rails strict_loading

How it can help you?

You can add #strict_loading! to any record to prevent lazy loading of associations. Strict loading 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.

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

On an association definition:

class User < ApplicationRecord
  has_many :comments, strict_loading: true
end

class Comment < ApplicationRecord
  belongs_to :user
end

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

Per model configuration:

class User < ApplicationRecord
  self.strict_loading_by_default = true

  has_many :comments
end

class Comment < ApplicationRecord
  belongs_to :user
end

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

If you want to enable strict loading by default you can do it with:

config.active_record.strict_loading_by_default = true

If you can change the action on strict loading violation from :raise (default) to :log you can do it with:

config.active_record.action_on_strict_loading_violation = :log

References

• ActiveRecord::Core
• ActiveRecord::QueryMethods
• Pull request: Add strict_loading mode to optionally prevent lazy loading

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.

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

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

References

• github/MiniProfiler/rack-mini-profiler
• The announcement posts from 2012

Bullet

How can it help you?

It will watch your queries while you develop your application and notify you when it detects one of this problems:

• 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

References

• github/flyerhzm/bullet

Prosopite

How can it help you?

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.

You can use it on the development and testing environments.

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>'

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
   ...

References

• github/palkan/n plus one control
• Squash N+1 queries early with n plus one control test matchers for Ruby and Rails

Related articles

• Tool to run the benchmark for fixes for the latest-comment n+1 queries problem
• What is the difference between includes, preload, eager_load and joins in ActiveRecord?
• A guide for preloading associations in rails
• Touch + fragment caching to avoid race conditions saving computed values