CODEWITHSUNDEEP
ruby-on-railsrubyactiverecordquery-optimization
Paul
Paul

Reputation: 163

Rails query optimization(eliminating n+1 queries)

I am working on a project and there is a complex query that takes 10 seconds or so to perform. I realize that there is an N + 1 query happening but I am new to rails and I am not sure how to fix it. The controller code is : 

def index

    filters = params.slice(:package_type, :guid)
    list = packages
    list = list.where(filters) unless filters.empty?

    respond_to do |format|
      format.html { @packages = list.includes(:classification).order(:priority => :asc) }
      format.json { @packages = list.includes(:classification, {channels: [:classification, :genres]}, :extras).order(:priority => :asc) }
    end
  end

the package model has 

class Package < ActiveRecord::Base
  extend FriendlyId


  belongs_to :classification
  has_many :package_channels
  has_many :channels, -> { order(:priority => :asc, :identifier => :asc) }, through: :package_channels
  has_many :package_extras
  has_many :extras, -> { order(:identifier => :asc) },through: :package_extras

the channels model has:

class Channel < ActiveRecord::Base

  belongs_to :classification
  has_many :channel_genres
  has_many :genres, through: :channel_genres
  has_many :package_channels
  has_many :packages, through: :package_channels

I also want to mention that filters is usually empty. If I am missing any info please feel free to comment and I will add it. Thanks for your time!

Here is the #packages method from the controller.

 def packages
    @plan ? @plan.packages : Package
  end

Here is the view: index.json.jbuilder

json.cache! ["cache", "#{params["plan_id"]}_packages_index"] do
  json.array! @packages do |package|
    json.partial! 'packages/package_lean', package: package
  end
end

Upvotes: 1

Views: 1232

Answers (1)

melekes
melekes

Reputation: 1888

I do not see the query itself, so I probably wont be able to answer specifically for this case.

1. Use Eager Loading to transform N+1 queries into 1

In general, your first step should be using eager loading technique to prevent N+1 queries. Most likely you are requesting associated collection (or single object) that is not yet loaded.

# controller
def index
  @customers = Customer.active
end

# view
<% @customers.each do |c| %>
  <%= c.name %> # this is fine the object is already in memory from your controller
  <%= c.address %> # this one makes a query to the database
<% end %>

This is usually solved by adding includes(association).

@customers = Customer.active.includes(:address)

2. Make sure you have an index for association's foreign key

Another good thing to have is an index for association's foreign key.

add_index :customer, :address_id

DB engine may choose not to use this index when building a plan of execution for some complex query, but, for a simple one, this is the case.

3. Use bullet gem

There is a badass gem called bullet. It will watch your queries while you develop your application and notify you when you should add eager loading (N+1 queries), when you're using eager loading that isn't necessary and when you should use counter cache.

Upvotes: 1

Related Questions