ruby-on-rails - Rails 优化查询并遍历大型实体

Question

我有一个方法可以输出以下哈希格式用于图表。

# Monthly (Jan - Dec)
{
  "john": [1,2,3,4,5,6,7,8,9,10,11,12],
  "mike": [1,2,3,4,5,6,7,8,9,10,11,12],
  "rick": [1,2,3,4,5,6,7,8,9,10,11,12]
}
# the indices represents the month
# e.g [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12]
# Index
# 0 = Jan
# 1 = Feb
# 2 = Mar
...

---

以下方法循环遍历给定年份内具有特定销售代表姓名的所有商店发票并生成上述结果

def chart_data
  hash = Hash.new {|h,k| h[k] = [] }

  (1..12).each do |month|
    date_range = "1/#{month}/#{date.year}".to_date.all_month
    all_reps.each do |name|
      hash[name] << store.bw_invoices.where(sales_rep_name: name, 
      purchase_date: date_range).sum(:subtotal).to_f
    end
  end
  return hash
end

---

当我运行这个方法时，它需要 4~5 秒的时间来执行。我真的需要优化这个查询。我想出了两个解决方案，我认为它们会有所帮助，但我很想了解您的一些专业知识。

1. 将其移至后台作业
2. 执行一个 SQL 查询来优化(如果这是最优的，我需要帮助)

非常感谢您的宝贵时间

Answer 1

是的，您发现了一个问题，如果不让数据库进行艰苦的工作，就很难有效解决。

假设您的数据集可能太大而无法将一整年的原始数据加载到 ruby​​ 对象中，这种仅使用一个 postgreSQL 查询的方法可能是最好的想法:

更多SQL方法

def chart_data
  result = Hash.new {|h,k| h[k] = [] }

  total_lines = store.bw_invoices.select("sales_rep_name, to_char(purchase_date, 'mm') as month, sum(subtotal) as total")
                                 .where(purchase_date: Date.today.all_year)
                                 .group("sales_rep_name, to_char(purchase_date, 'mm')")                                 

  total_lines.each do |total_line|
    result[total_line.sales_rep_name][total_line.month.to_i - 1] = total_line.total.to_f
  end

  result
end

请注意，对于代表没有销售的月份，此解决方案将保留 nil 而不是 0。如果他们的最后一个月销售是 6 月，那么数组中将只有 6 个项目。

我们可以通过更复杂的 SQL 从虚拟表左连接或通过随后填充数组间隙来避免这种情况。但是，根据您设置图表的方式，这可能没有任何实际区别。

更多ruby方法

def chart_data
  result = Hash.new {|h,k| h[k] = [] }

  (1..12).each do |month|
    date_range = "1/#{month}/#{Date.today.year}".to_date.all_month
    rows = store.bw_invoices.select("sales_rep_name, SUM(subtotal) as total")
                            .where(purchase_date: date_range)
                            .group(:sales_rep_name)

    all_reps.each do |rep_name|
      row = rows.detect { |x| x.sales_rep_name == rep_name }
      result[rep_name] << (row ? row.total : 0).to_f
    end
  end

  result
end

这与您的方法更相似，但在内部循环之外进行查询，因此我们进行 12 次查询而不是 12 * 重复次数。使用的 detect 可能会变得有点慢，但前提是有数千次重复。在这种情况下，您可以对 all_reps 和查询输出进行排序并实现您自己的类型 merge join但那时你会变得很复杂，你不妨让数据库再次处理。