Benito Serna Tips and tools for Ruby on Rails developers

Tools to help you detect n+1 queries

March 1, 2021, updated on July 12, 2023

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:

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

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:

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.

References

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:

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

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:

FactoryBot.create_list(:leg, 10)

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

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

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

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

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

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

Related articles

Download a free ebook to learn the basics of n+1 queries on Rails basics

Learn just enough fundamentals to be fluent preloading associations with ActiveRecord, and start helping your team to avoid n+1 queries on production.