update_all with a method

Question

Lets say I have a model:

class Result < ActiveRecord::Base
  attr_accessible :x, :y, :sum
end

Instead of doing

Result.all.find_each do |s|
  s.sum = compute_sum(s.x, s.y)
  s.save
end

assuming compute_sum is a available method and does some computation that cannot be translated into SQL.

def compute_sum(x,y)
  sum_table[x][y]
end

Is there a way to use update_all, probably something like:

Result.all.update_all(sum: compute_sum(:x, :y))

I have more than 80,000 records to update. Each record in find_each creates its own BEGIN and COMMIT queries, and each record is updated individually.

Or is there any other faster way to do this?

Answer 1

If the compute_sum function can't be translated into sql, then you cannot do update_all on all records at once. You will need to iterate over the individual instances. However, you could speed it up if there are a lot of repeated sets of values in the columns, by only doing the calculation once per set of inputs, and then doing one mass-update per calculation. eg

Result.all.group_by{|result| [result.x, result.y]}.each do |inputs, results|
  sum = compute_sum(*inputs)
  Result.update_all('sum = #{sum}', "id in (#{results.map(&:id).join(',')})")
end

You can replace result.x, result.y with the actual inputs to the compute_sum function.

EDIT - forgot to put the square brackets around result.x, result.y in the group_by block.