Updating many rows from a hash without just looping in Rails

Question

I have a hash which has in excess of a thousand key/value pairs.

I have a database table with several thousand rows.

So to brute-force an update of the table based on the hash is pretty simple. e.g. :

my_hash.each{|key,value|
   Model.update_all("column2 = #{value}", "column1 = #{key}")
}

But this will do over a thousand SQL update statements.

Is there any good way (in Rails) to do this with one (or just a few) update statements?

Answer 1

Yes. I don't know specifically about rails, but you can use the IN sql keyword, which works like:

select * from table where column in ('list', 'of', 'values')

Answer 2

The fastest way -- in any language, don't know how it's done in Rails specifically -- will be to dump your in-memory hash into a temporary table in the database, which only has columns for your hash's keys and values, then issue a single UPDATE command with a join that links your table to be updated with the new temp table.

Check the query plan to ensure that the optimizer works as it should, building a temporary index on your temp table before issuing the update. If it doesn't, build the index yourself.

Answer 3

I'm not sure about "good", but on many DBMS platforms it's possible to send multiple SQL statements in a single execution. The ar-extensions gem, for example, can help to batch INSERT statements, although depending on platform it may need a little tweaking - Oracle, for example, is a little tricky. I don't think it handles UPDATEs directly, but you could look into using the temp table trick from another answer here: batch load the data and run an UPDATE using ActiveRecord.execute.

Possibly only "good" in the sense that it's probably possible, but then again, Rails (or more accurately ActiveRecord) never intended that the ORM functions be used for absolutely everything, that's why things like find_by_sql exist: they're there for the times when you know a better way.

Answer 4

Well I have solved this with the hints from Mike Woodhouse and ttarchala. Here's the code (I am using the Ruby Sequel gem)

# connect to the database
DB = Sequel.connect("postgres://user:password@localhost/blah")

# create the temporary table
DB.create_table :temp_update, :temp => true do
  primary_key :key
  String :value
end

# insert rows from the hash (multi_insert takes an array of hashes,hence the map)
DB[:temp_update].multi_insert(my_hash.map{|k,v| {:key => k, :value => v}})

# Do the update based on a join
DB.execute("  UPDATE my_table 
              SET column2 = temp_update.value 
              FROM temp_update
              WHERE column1 = temp_update.id")

I am using Postgres, so the exact update SQL may be different for other RDBMSs.