Query Optimization with ActiveRecord for each method

Question

Below mentioned query is taking too much time, not able to understand how to optimized it.

Code and Associations :

temp = []
platforms = current_user.company.advisory_platforms
platforms.each{ |x| temp << x.advisories.published.collect(&:id) }

class Advisory
  has_many :advisory_platforms,:through =>:advisory_advisory_platforms
end

class AdvisoryPlatform
  has_many :companies,:through => :company_advisory_platforms
  has_many :company_advisory_platforms,:dependent => :destroy
  has_many :advisory_advisory_platforms,:dependent => :destroy
  has_many :advisories, :through => :advisory_advisory_platforms
end

http://guides.rubyonrails.org/active_record_querying.html#eager-loading-associations — max, May 17 '16 at 09:26
Tried temp = platforms.includes(:advisories).published.collect(&:id)};0 but it returns platforms, I want advisories in output, the output stored in temp variable. — Lussi, May 17 '16 at 09:40
You should always specify what version of Rails you are using so that we know which features you can use. — Adam Lassek, May 18 '16 at 19:19

score 0 · Answer 1 · edited May 23 '17 at 12:23

There are three glaring performance issues in your example.

First, you are iterating the records using each which means that you are loading the entire record set into memory at once. If you must iterate records in this way you should always use find_each so it is done in batches.

Second, every iteration of your each loop is performing an additional SQL call to get its results. You want to limit SQL calls to the bare minimum.

Third, you are instantiating entire Rails models simply to collect a single value, which is very wasteful. Instantiating Rails models is expensive.

I'm going to solve these problems in two ways. First, construct an ActiveRecord relation that will access all the data you need in one query. Second, use pluck to grab the id you need without paying the model instantiation cost.

You didn't specify what published is doing so I am going to assume it is a scope on Advisory. You also left out some of the data model so I am going to have to make assumptions about your join models.

advisory_ids = AdvisoryAdvisoryPlatform
  .where(advisory_platform_id: current_user.company.advisory_platforms)
  .where(advisory_id: Advisory.published)
  .pluck(:advisory_id)

If you pass a Relation object as the value of a field, ActiveRecord will convert it into a subquery.

So

where(advisory_id: Advisory.published)

is analogous to

WHERE advisory_id IN (SELECT id FROM advisories WHERE published = true)

(or whatever it is published is doing).

Query Optimization with ActiveRecord for each method

1 Answers1