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

Question

In my Rails application, 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 payments a user that have ever been received, sorted by year, quarter or month. payments also divided 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 also creates a lot of SQL queries. In a typical dashboard view, I get 100+ SQL queries. Before adding .includes(:invoice) there were even more requests.

I assume that one of the main problems is that each subtotal , total_tax and total invoice is not stored anywhere in the database, but is calculated with every request.

Can someone tell me how to speed things up here? I am not very familiar with SQL and the inner workings of ActiveRecord, so this is probably the problem here.

Thanks for any help.

Answer 1

Whenever revenue_between is revenue_between , it retrieves payments in the given time range and the associated invoices and items from db. Since time ranges have many overlaps (month, quarter, year), the same records are retrieved again and again.

I think it’s best to get all user payments once and then filter and sum them in Ruby.

To implement, modify 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 will require downloading all payments along with the corresponding invoices and goods.

Also change invoice summarization methods to use 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

In addition to the memoir strategy proposed by @tihom, I suggest you take a look at the Bullet gem , which, as the description says, will help you kill N + 1 requests and unused downloads.

Answer 3

Most of your data does not have to be in real time. You may have a service that calculates statistics and stores it wherever you want (Redis, cache ...). Then update them every 10 minutes or at the request of the user.

First, make your page without statistics and load it with ajax.