Jeff Kreeftmeijer for AppSignal

Posted on Apr 25, 2018 • Updated on Jun 4, 2018 • Originally published at blog.appsignal.com

ActiveRecord performance: the N+1 queries antipattern

#beginners #rails #performance #ruby

A lot of ORMs, like Rails' ActiveRecord, have lazy loading built in to allow you to defer querying associations until the moment they're needed. It allows being implicit about which associations need to be loaded by offloading this decision to the view.

The N+1 queries problem is a common, but usually easy to spot, performance antipattern that results in running a query for each association, which causes overhead when querying a large number of associations from the database.

Lazy loading in ActiveRecord

ActiveRecord uses implicit lazy loading to make it easier to work with relations. Let's consider the webshop example, where each Product can have any number of Variants which contain the product's color or size, for example.

# app/models/product.rb
class Product < ActiveRecord::Base
  has_many :variants
end

In ProductsController#show, the detail view for one of the products, we'll use Product.find(params[:id]) to get the product and assign it to the @product variable.

# app/controllers/products_controller.rb
class ProductsController < ApplicationController
  def show
    @product = Product.find(params[:id])
  end
end

In the view for this action, we'll loop over the product's variants by calling the variants method on the @product variable we received from the controller.

# app/views/products/show.html.erb
<h1><%= @product.title %></h1>

<ul>
<%= @product.variants.each do |variant| %>
  <li><%= variant.name %></li>
<% end %>
</ul>

By calling @product.variants in the view, Rails will query the database to get the variants for us to loop over. Aside from the explicit query we did in the controller, we can see another query is executed to fetch the variants if we check Rails' logs for this request.

Started GET "/products/1" for 127.0.0.1 at 2018-04-19 08:49:13 +0200
Processing by ProductsController#show as HTML
  Parameters: {"id"=>"1"}
  Product Load (1.1ms)  SELECT  "products".* FROM "products" WHERE "products"."id" = ? LIMIT ?  [["id", 1], ["LIMIT", 1]]
  Rendering products/show.html.erb within layouts/application
  Variant Load (1.1ms)  SELECT "variants".* FROM "variants" WHERE "variants"."product_id" = ?  [["product_id", 1]]
  Rendered products/show.html.erb within layouts/application (4.4ms)
Completed 200 OK in 64ms (Views: 56.4ms | ActiveRecord: 2.3ms)

This request executed two queries to show a product with all of its variants.

SELECT "products".* FROM "products" WHERE "products"."id" = 1 LIMIT 1
SELECT "variants".* FROM "variants" WHERE "variants"."product_id" = 1

Looped lazy loading

Lazy loading has been great so far. By using an implicit query, we don't have to remember to remove it from the controller when we decide we don't want to show the variants on this view anymore, for example.

Let's say we're working on ProductsController#index, where we'd like to show a list of all products with each of their variants. We can implement that with lazy loading the same way as we did before.

# app/controllers/products_controller.rb
class ProductsController < ApplicationController
  def index
    @products = Product.all
  end
end

# app/views/products/index.html.erb
<h1>Products</h1>

<% @products.each do |product| %>
<article>
  <h1><%= product.title %></h1>

  <ul>
    <% product.variants.each do |variant| %>
      <li><%= variant.description %></li>
    <% end %>
  </ul>
</article>
<% end %>

Unlike the first example, we now get a list of products from the controller instead of a single one. The view then loops over each product, and lazy loads each variant for each product.

While this works, there is one catch. Our query count is now N+1.

N+1 queries

In the first example, we rendered a view for a single product and its variants. The query count was 2 because we executed two queries. This request returned all products (3, in this example) from the database, and each of their variants, and it did four queries instead of two.

Started GET "/products" for 127.0.0.1 at 2018-04-19 09:49:02 +0200
Processing by ProductsController#index as HTML
  Rendering products/index.html.erb within layouts/application
  Product Load (0.3ms)  SELECT "products".* FROM "products"
  Variant Load (0.2ms)  SELECT "variants".* FROM "variants" WHERE "variants"."product_id" = ?  [["product_id", 1]]
  Variant Load (0.2ms)  SELECT "variants".* FROM "variants" WHERE "variants"."product_id" = ?  [["product_id", 2]]
  Variant Load (0.1ms)  SELECT "variants".* FROM "variants" WHERE "variants"."product_id" = ?  [["product_id", 3]]
  Rendered products/index.html.erb within layouts/application (5.6ms)
Completed 200 OK in 36ms (Views: 32.6ms | ActiveRecord: 0.8ms)

SELECT "products".* FROM "products"
SELECT "variants".* FROM "variants" WHERE "variants"."product_id" = 1
SELECT "variants".* FROM "variants" WHERE "variants"."product_id" = 2
SELECT "variants".* FROM "variants" WHERE "variants"."product_id" = 3

The first query, which is executed by the explicit call to Product.all in the controller, finds all products. The subsequent ones are lazily executed while looping over each product in the view.

This example results in a query count of N+1, where N is the number of products, and the added one is the explicit query that fetched all products. In other words; this example does one query, and then another one for each of the results in the first query. Because N = 3 in this example, the resulting query count is N + 1 = 3 + 1 = 4.

While this might not really be a problem when having only three products, the query count goes up with the number of products. Because we know this request has N+1 queries, we can predict a query count of 101 when we have 100 products (N + 1 = 100 + 1 = 101), for example.

Eager loading associations

Instead of increasing the number of queries with the number of products like we do now, we'd like to have a static number of requests in this view. We can do that by explicitly preloading the variants in the controller before rendering the view.

# app/controllers/products_controller.rb
class ProductsController < ApplicationController
  def index
    @products = Product.all.includes(:variants)
  end
end

ActiveRecord's includes query method makes sure the associated variants are loaded with their products. Because it knows which variants need to be loaded beforehand, it can fetch all variants of all requested products in one query.

Started GET "/products" for 127.0.0.1 at 2018-04-19 10:33:59 +0200
Processing by ProductsController#index as HTML
  Rendering products/index.html.erb within layouts/application
  Product Load (0.3ms)  SELECT "products".* FROM "products"
  Variant Load (0.4ms)  SELECT "variants".* FROM "variants" WHERE "variants"."product_id" IN (?, ?, ?)  [["product_id", 1], ["product_id", 2], ["product_id", 3]]
  Rendered products/index.html.erb within layouts/application (5.9ms)
  Completed 200 OK in 45ms (Views: 40.8ms | ActiveRecord: 0.7ms)

By preloading the variants, the query count drops back to 2, even if the number of products increases in the future.

SELECT "products".* FROM "products"
SELECT "variants".* FROM "variants" WHERE "variants"."product_id" IN (1, 2, 3)

Lazy or eager?

In most situations, getting all associated records from the database in a single query is a lot faster than lazy loading them.

In this example application, the database performance difference is measurable with only three products, each having ten variants. On average, eager loading the products list is about 12.5% faster (0.7 ms vs 0.8 ms) than lazy loading. With ten products, that difference jumps to 59% (1.22 ms vs 2.98 ms). With 1000 products, the difference is almost 80%, as the eager queries clock in at 58.4 ms, while lazy loading them takes about 290.12 ms.

Although lazily-loaded associations give more flexibility in the view without having to update the controller, a good rule of thumb is to have the controller handle loading the data before passing it off to the view.

Lazy loading from the view works for views that show one model object and it's associations (like the ProductsController#show in our first example) and can be useful when having multiple views that require different data from the same controller, for example.

In short: always keep an eye on the development logs, or the event timeline in AppSignal, to make sure you're not doing queries that could be lazy loaded and keep track of your response times, especially when the amount of data that's processed increases.

If you liked this article, check out more of what we wrote on AppSignal Academy. AppSignal is all about building better apps. In our Academy series, we'll explore application stability and performance, and explain core programming concepts.

We'd love to know what you thought of this article, or if you have any questions. We're always on the lookout for topics to investigate and explain, so if there's anything magical in Ruby you'd like to read about, don't hesitate to leave a comment.