Benito Serna Tips and tools for Ruby on Rails developers

Example app to understand why some times fixing some n+1 queries can hurt performance

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.

Run the app

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

Fix n+1 queries

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

Filter the posts by number of comments

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.

Finding the request time and allocations

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)

Compare the request time

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.

Compare the allocations

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.

How to fix your n+1 queries fix

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:

Related articles

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

Sign up to download my free ebook, where I will show you how to deal with n+1 problems like a pro, by helping you...

  • Understand the principal 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 before they hit production (like the bullet gem).