Type a keyword and hit enter to start searching. Press Esc to cancel.

Currently Reading

Decrease ActiveRecord Call Counts By Avoiding n+1 Queries On Associations

ActiveRecord is awesome! However, it’s not without a few downsides and common pitfalls. The simplicity sometimes comes at a cost of efficiency. A great example is the n+1 problem when scoping an ActiveRecord relation by a belongs_to association.

For example, take the following associated Models

class Employee < ActiveRecord::Base
  belongs_to :company
end

class Company < ActiveRecord::Base
 has_many :employees
 has_many :offices
end

If you were using an Employee instance method to find company offices for the Employee, you may do something like this:

Office.where(company: self.company)

Or you could say:

Office.where(company_id: self.company_id)

Both examples will give you the following query:

SELECT * FROM offices WHERE company_id = ?

However, the first example will have to load the Company from the database, instead of just referencing the company_id attribute. The second example is only referencing attributes of the current object and doesn’t need to know about the associated instance of Company.

I ran across this problem when investigating transaction performance on some background processes. Using New Relic, I was able to dig into a specific time frame and see various metrics on all the queries being performed.

You may be thinking, “What about has_many through?!”. It’s a valid point and it avoids the n+1, however, this is not always possible. When working with an Engine or gem, the association may not be defined.

This can be pretty helpful when you are running millions of queries a day. In my case, it cut query throughput on one table from 30k cpm to 20k cpm, that’s a 30% reduction with very minor code changes! This in turn increased throughput on the ‘meaty’ transactions that were actually processing data.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *