如何（大型）减少Rails应用程序的SQL查询的数量？ [英] How to (massively) reduce the number of SQL queries in Rails app?

问题描述

在我的Rails应用程序我有用户这可以有很多发票这反过来又可以有许多支付。

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

现在在仪表板观点我想总结一下所有的支付 A 用户曾经接受，无论是订购按年，季度或月。该支付也被细分成的总值（GDP）的净和税的

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) %>

现在的问题是，这种方法有效，但会产生非常多的SQL查询为好。在一个典型的仪表板查看我得到的 100 + SQL查询。然后加入 .includes（：发票）还有更多的疑问

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.

我认为主要的问题之一是，每一张发票的小计， total_tax 和总不存储任何地方在数据库中，而是计算每一个请求。

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.

谁能告诉我怎么在这里的事情加快？我不是太熟悉SQL和ActiveRecord的内部工作，所以这可能是这里的问题。

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.

感谢您的帮助。

推荐答案

当 revenue_between 被调用，它取了支付在给定时间范围和相关的发票和项目从分贝。由于时间段有很多重叠（月，季，年），相同的记录被读取一遍又一遍。

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.

我觉得这是更好地获取用户的所有支付一次，然后过滤和Ruby中总结了他们。

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

要落实，更改 revenue_between 的方法如下：

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

这篇关于如何（大型）减少Rails应用程序的SQL查询的数量？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！