Knowledge for the World

How to identify and fix N+1 queries in your Rails app using Scout APM

The N+1 problem occurs when at least two objects are stored in a database with a one-to-many relationship and queries are performed against the "one" table, then separately n number of times against the related object, n being the number of related objects.

To illustrate this, I'll use the analogy of a meal website and a page where you wish to display the meals.

In the controller:

@meals = Meal.where(status=1)
# approximate query: SELECT * FROM meals WHERE status = 1;

In the view:

<% @meal.each do |meals| %>
    <div class="meal-row">
      <h4><%= meal.name %><br />
        <%= link_to meal.user.username, user_path(meal.user.username) %>
        <%# approximate query: SELECT * FROM users WHERE user.id = {{ meal_id }}; %>
      </h4>
      <p><%= meal.description %></p>
      <%= link_to 'Cook This Now', meal %>
  </div>
<% end %>

Notice how we're creating a link to meal.user.username.

To approximate the queries used in this page, we have the following:

SELECT * FROM meals WHERE status = 1; # 1 time, yielding n rows
SELECT * FROM users WHERE user.id = {{ meal_id }}; # n times

The ORM in Rails should do a lot of work to minimize the net slowness of this problem such as database indexing and caching template fragments, but nothing will change the fact that you are querying the database in a loop.

Now let's take a look at how to find these in your code and how to get rid of them.

In these interests [?]
  • scout
    0 Subscribers Subscribe
  • webdev
    124 Subscribers Subscribe
  • rails
    3 Subscribers Subscribe
1

Load up Scout APM

When you load up Scout APM, you'll see a graph of recent requests. They are segmented by the different parts of your application e.g. Middleware, ActiveRecord, Ruby, and Request Queuing.

Load up Scout APM
2

Find Slow Requests

You can drill down into slow requests only by clicking "What's Slow" at the top or by dragging your mouse across the graph to select requests and clicking "See 4 slow requests" if applicable.

Find Slow Requests
3

Choose a slow endpoint

It should be fairly easy to predict which endpoints might be making too many database calls. Pick one and load it up.

4

Find N+1 calls

On the next page, you get a nice breakdown of this endpoint based on the requests from the time period chosen. You can also select individual requests on the left for more detail.

The important part I want to direct your attention to is the orange N+1 icon. This means that within my 'home/index' controller, I'm making 500 calls to User#find (User.find or similar) and its taking an average of 1,367.9 ms.

Clicking the 'SQL' button will show you the problem query.

In my case, it's

SELECT `users`.* FROM `users` WHERE `users`.`id` = ? LIMIT 1

This means I'm querying for users inside of a loop.

Find N+1 calls
5

Find suspect code

If you are using Scout's Github integration, you can see down to the line of code where the query is originating from. Click the 'CODE' icon to get this UI.

Find suspect code
6

Fix the problem

Fixing an N+1 call is fairly simple with Rails. You need to employ the help of something called "eager loading". Eager loading allows you to specify in advance which associated models you plan on using. Do this using the "includes" method of Model.

Before:

@meals = Meal.where(visibility: 1).limit(500)

After:

@meals = Meal.includes(:user).where("visibility = 1").limit(500)

You do not need to change the way you're accessing the data later in your views. Rails takes care of that for you.

7

Test and measure

Test these changes locally and run your unit tests. After deploying, check Scout APM to make sure you've eliminated the N+1 queries.