I want to share with you an example application to help you visualize why “fixing” an n+1 queries problem, can hurt the performance of your application if you preload assocations with too many records.
It simulates the index page of a blog application. And appart from the rack-mini-profiler gem, it shows the logs in the page to help you visualize easily the queries, request time and allocations.
By default it has an n+1 queries problem. It makes a call to the database to fetch the latest comment for each post in the list, but you can fix it with a checkbox.
It will let you compare request times combining a filter of posts by number of comments and the checkbox to “fix” the n+1 queries problem.
You can access the code on github, on bhserna/why_n_plus_one_fix_hurts_performance.
The app uses:
To use the app effectively you need to run the seeds.rb because it will create the posts that you need to test the app.
seeds = Seeds.new
seeds.create_posts 20, comments_count: 5
seeds.create_posts 20, comments_count: 10
seeds.create_posts 20, comments_count: 50
seeds.create_posts 20, comments_count: 100
seeds.create_posts 20, comments_count: 1000
seeds.create_posts 20, comments_count: 10000
(Don’t worry it is semi-fast because it uses insert_all)
You can update that file to test other combinations.
To create and initialize the database for the first time you can use:
./bin/rails db:setup
If you want to reset the database and run new seeds, you can use:
./bin/rails db:reset
This is code to display each post…
<% @posts.each do |post| %>
<div class="post">
<h1><%= post.title %></h1>
<p>💬 <%= number_with_delimiter post.comments.size %>
<div class="comment">
<p><strong>Latest comment:</strong> <%= post.comments.last.body %></p>
</div>
</div>
<% end %>
As you can see… if we don’t preload the comments, ActiveRecord will make a new database call for each post in this line…
<p><strong>Latest comment:</strong> <%= post.comments.last.body %></p>
The checkbox with label “Fix n+1 queries?” will fix the n+1 queries doing
preload(:comments) on the controller.
def index
@posts = Post.where(comments_count: current_filter)
if fix_n_plus_one_queries?
@posts = @posts.preload(:comments)
end
end
In the page we have a select field that will display just the posts with the selected number of comments. We have posts with 5, 10, 50, 100, 1,000 and 10,000.
For each “number of comments” option we have 20 posts.
You can use this select to play with different combinations of number of comments, with and without the fix.
You can see the request time and allocations on the logs, if you look for something like this:
Completed 200 OK in 92ms (Views: 50.9ms | ActiveRecord: 23.8ms | Allocations: 24737)
You can also see query times:
Post Load (4.2ms) SELECT "posts".* FROM "posts"....
And template rendering times:
Rendered posts/index.html.erb within layouts/application (Duration: 58.0ms | Allocations: 18269)
If you compare different combiantions of number of comments, you will see that “fixing” the n+1 queries problem, sometimes will produce faster responses, but not always.
And for some cases, even when it is faster it could not be enough for your needs, and maybe is time to look for other ways to solve the problem.
Nate Berkopec has a rule of thumb…
<10,000 = don’t sweat it
10k->100,000k = might matter if you loop, but also don’t worry about it
100k->1mil = reducing objects would speed this up considerably
1mil->10mil = This is increasing your process’ memory + is very slow
>10mil = ☠️
You can play with the app and check the number of allocations that it is producing for each request with differente combinations.
If are in the situation where your fix is not really a fix or that is not good enough. There are several ways in that you can fix your problem.
For a lot of this cases the first step is to implement a counter_cache but when you really need to preload the data because you need to something more than a counter, you can give a look to:
Learn just enough fundamentals to be fluent preloading associations with ActiveRecord, and start helping your team to avoid n+1 queries on production.