N+1 queries issue
The N+1 query issue is common performance problem in Rails applications.
To better understand it let’s consider typical e-commerce application where we’re dealing with products with prices in multiple currencies.
class Product < ActiveRecord::Base
has_many :prices
end
class Price < ActiveRecord::Base
belongs_to :product
validates :currency_code, presence: true
end
When we want to display all products and show price in USD we can do something like this
Product.all.each do |product|
product.prices.where(currency_code: 'USD').cents
end
Above code will result in 1 query to fetch all products and 1 query for each product to fetch price in USD
select * from products;
select * from prices where product_id = 1 and currency_code = 'USD'
select * from prices where product_id = 2 and currency_code = 'USD'
select * from prices where product_id = 3 and currency_code = 'USD'
-- ...
This will make your database unhappy and your application slow.
Eager loading to the rescue
The easiest way to fix N+1 queries is to use association’s eager loading provided by ActiveRecord (here is excellent article describing all 3 available options and I strongly recommend to read it!)
Product.all.includes(:prices).each do |product|
product.prices.find { |price| price.currency_code == 'USD' }.cents
end
Now our queries will look like this
select * from products;
select * from prices where product_id in (1,2,3,...)
Great, no more N+1 issue! On the second look though we’re still loading more data than we actually need.
We just need to show prices in USD and yet we also loaded all other currencies.
Multiple associations
In ActiveRecord we can pass where condition to association. That means we can have usd_prices,
eur_prices, etc. and use that in includes.
Here’s refactored example:
class Product < ActiveRecord::Base
has_many :prices
has_many :usd_prices, -> { where(currency_code: 'USD') }
has_many :eur_prices, -> { where(currency_code: 'EUR') }
# ...
end
Product.all.includes(:usd_prices).each do |product|
product.usd_prices.first.cents
end
select * from products;
select * from prices where currency_code = 'USD' and product_id in (1,2,3,...)
This solution is almost ideal since we don’t have N+1 issue and we only load data that we’re going to use. But It’s not flexible: if we want to handle new currency we have to add association, reload code and so on.
This could be acceptable for currency because we won’t be adding new ones very often but there might be situations that would require more dynamic approach.
Preloading with dynamic scope
We cannot pass dynamic conditions to ActiveRecord’s associations so we need to dig a little deeper and discover ActiveRecord::Associations::Preloader api.
# Eager loads the named associations for the given Active Record record(s).
#
# In this description, 'association name' shall refer to the name passed
# to an association creation method. For example, a model that specifies
# <tt>belongs_to :author</tt>, <tt>has_many :buyers</tt> has association
# names +:author+ and +:buyers+.
#
# == Parameters
# +records+ is an array of ActiveRecord::Base. This array needs not be flat,
# i.e. +records+ itself may also contain arrays of records. In any case,
# +preload_associations+ will preload the all associations records by
# flattening +records+.
#
# +associations+ specifies one or more associations that you want to
# preload. It may be:
# - a Symbol or a String which specifies a single association name. For
# example, specifying +:books+ allows this method to preload all books
# for an Author.
# - an Array which specifies multiple association names. This array
# is processed recursively. For example, specifying <tt>[:avatar, :books]</tt>
# allows this method to preload an author's avatar as well as all of his
# books.
# - a Hash which specifies multiple association names, as well as
# association names for the to-be-preloaded association objects. For
# example, specifying <tt>{ author: :avatar }</tt> will preload a
# book's author, as well as that author's avatar.
#
# +:associations+ has the same format as the +:include+ option for
# <tt>ActiveRecord::Base.find</tt>. So +associations+ could look like this:
#
# :books
# [ :books, :author ]
# { author: :avatar }
# [ :books, { author: :avatar } ]
ActiveRecord::Associations::Preloader.new.preload(records, associations, preload_scope = nil)
The third option - preload_scope - is not documented, but it provides us with the ability to filter prices
with currency.
products = Product.all.to_a
ActiveRecord::Associations::Preloader.new.preload(products, :prices, Price.where(currency_code: 'USD'))
products.each do |product|
product.prices.first.cents
end
Generated queries are identical to the ones from prev. example
select * from products;
select * from prices where currency_code = 'USD' and product_id in (1,2,3,...)
but since we’re passing the currency dynamically we can add new one on the fly!
