This is a little reference of tools to help you detect n+1 queries on a rails app.
It will watch your queries while you develop your application and notify you when it detects a problem.
It can detect:
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.
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
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.
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:
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
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>'
Add this line to your application’s Gemfile:
gem 'prosopite'
And then execute:
$ bundle install
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
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
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.
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
...
Add it to your application’s Gemfile and run bundle install:
group :test do
gem "n_plus_one_control"
end
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
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.
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
strict_loading
mode to optionally prevent lazy loadingIt 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…
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.
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'
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
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.
Sign up to download free ebook, where I will show the basics on how to deal with n+1 problems.
joins
, includes
, etc...) to work with associations.