How to (massively) reduce the number of SQL queries in Rails app?

Question

In my Rails app I have users which can have many invoices which in turn can have many payments.

Now in the dashboard view I want to summarize all the payments a user has ever received, ordered either by year, quarter, or month. The payments are also subdivided into gross, net, and tax.

user.rb:

class User < ActiveRecord::Base

  has_many  :invoices
  has_many  :payments

  def years
    (first_year..current_year).to_a.reverse
  end

  def year_ranges
    years.map { |y| Date.new(y,1,1)..Date.new(y,-1,-1) }
  end

  def quarter_ranges
    ...
  end

  def month_ranges
    ...
  end

  def revenue_between(range, kind)
    payments_with_invoice ||= payments.includes(:invoice => :items).all
    payments_with_invoice.select { |x| range.cover? x.date }.sum(&:"#{kind}_amount") 
  end

end

invoice.rb:

class Invoice < ActiveRecord::Base

  belongs_to :user
  has_many :items
  has_many :payments

  def total
    items.sum(&:total)
  end

  def subtotal
    items.sum(&:subtotal)
  end

  def total_tax
    items.sum(&:total_tax)
  end

end

payment.rb:

class Payment < ActiveRecord::Base

  belongs_to :user
  belongs_to :invoice  

  def percent_of_invoice_total
    (100 / (invoice.total / amount.to_d)).abs.round(2)
  end

  def net_amount
    invoice.subtotal * percent_of_invoice_total / 100
  end  

  def taxable_amount
    invoice.total_tax * percent_of_invoice_total / 100
  end

  def gross_amount
    invoice.total * percent_of_invoice_total / 100
  end

end

dashboards_controller:

class DashboardsController < ApplicationController

  def index    
    if %w[year quarter month].include?(params[:by])   
      range = params[:by]
    else
      range = "year"
    end
    @ranges = @user.send("#{range}_ranges")
  end

end

index.html.erb:

<% @ranges.each do |range| %>

  <%= render :partial => 'range', :object => range %>

<% end %>

_range.html.erb:

<%= @user.revenue_between(range, :gross) %>
<%= @user.revenue_between(range, :taxable) %>
<%= @user.revenue_between(range, :net) %>

Now the problem is that this approach works but produces an awful lot of SQL queries as well. In a typical dashboard view I get 100+ SQL queries. Before adding .includes(:invoice) there were even more queries.

I assume one of the major problems is that each invoice's subtotal, total_tax and total aren't stored anywhere in the database but instead calculated with every request.

Can anybody tell me how to speed up things here? I am not too familiar with SQL and the inner workings of ActiveRecord, so that's probably the problem here.

Thanks for any help.

Answer 1

Whenever revenue_between is called, it fetches the payments in the given time range and the associated invoices and items from the db. Since the time ranges have lot of overlap (month, quarter, year), same records are being fetched over and over again.

I think it is better to fetch all the payments of the user once, then filter and summarize them in Ruby.

To implement, change the revenue_between method as follows:

def revenue_between(range, kind) 
   #store the all the payments as instance variable to avoid duplicate queries
   @payments_with_invoice ||= payments.includes(:invoice => :items).all
   @payments_with_invoice.select{|x| range.cover? x.created_at}.sum(&:"#{kind}_amount") 
end

This would eager load all the payments along with associated invoices and items.

Also change the invoice summation methods so that it uses the eager loaded items

class Invoice < ActiveRecord::Base

   def total
     items.map(&:total).sum
   end

   def subtotal
     items.map(&:subtotal).sum
   end

   def total_tax
     items.map(&:total_tax).sum
   end

end

Answer 2

Apart from the memoizing strategy proposed by @tihom, I suggest you have a look at the Bullet gem, that as they say in the description, it will help you kill N+1 queries and unused eager loading.

Answer 3

Most of your data do not need to be real time. You can have a service calculating the stats and storing them wherever you want (Redis, cache...). Then refresh them every 10 minutes or upon user's request.

In the first place, render your page without stats and load them with ajax.