N+1 Queries and Eager Loading

Ruby on Rails
1

I am trying to get a better understanding of N+1 Queries, I am aware of them and eager loading but I have no idea what causes them to prevent it.

At the moment I am relying on the Bullet Gem to tell me. Can somebody please explain this a little better?

Thanks

2

Imagine I have these models:

class Category
  has_many :products
end

class Product
  belongs_to :category
end

I want to list all of my products with their category, so I do something like this:

Product.all.each do |product|
  puts product.name
  puts "In category: #{product.category.name}"
end

Rails will perform one query to load all of the products, and then each time round the loop we ask it for the category associated with the product so it does another query to get that one category. If we have N products, we’re doing 1 query to load the products and the N queries to load the categories one by one, hence N+1.

If we add eager loading we’re telling Rails up front that we need all of the categories associated with these products, so we can reduce N+1 queries to 2 queries, one for the products and one for the categories:

Product.includes(:category).each do |product|
  puts product.name
  puts "In category: #{product.category.name}"
end

In this example everything’s contains in a few lines, so it’s easy to spot. In a real Rails app it’s common that the query happens in a controller, the loop happens in a view, and the reference to the association happens in a partial, which makes it difficult to notice sometimes without looking at the generated queries in the server log, or using something like bullet.

4 Likes
3

Thanks @georgebrock