Dahlia Bock

ActiveRecord: Avoid N + 1 queries

Ruby 1.8.7, Rails 2.3.8

I made a rookie mistake a few days ago but thanks to a colleague of mine who smacked me on the hand, I had a chance to correct it. Consider the following model:

class Order < ActiveRecord::Base
  belongs_to :account
end

class Account < ActiveRecord::Base
  has_many :orders
end

If you are retrieving a group of accounts and accessing each of its orders, you would be encountering an N + 1 query problem. Meaning you would be firing 1 query to retrieve 1 account, and if that account has N orders, N + 1 queries would be executed.

accounts = Account.find(:all, :limit => 10)
 
accounts.each do |a|
  puts a.orders
end

ActiveRecord uses default_scope to avoid the N + 1 situation.

class Account < ActiveRecord::Base
  default_scope :include => :order
  has_many :orders
end

This will execute just 2 queries:

SELECT "accounts".* FROM "accounts" LIMIT 10
SELECT "orders".* FROM "orders" WHERE "orders"."account_id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

The :include option can be used in a named scope as well. Long running queries are bad (of course), especially when you are dealing with tens of thousands of records.

Lesson well learnt (and remembered). Thanks Blake.