Bulk insertion of data with ActiveRecord

Posted by glenn on Wednesday, June 18, 2008

Posted in optimization, activerecord, rubyonrails

Apologies to all for the extended hiatus, I was in the US for a month on holiday; and then I just got lazy. Anyway, I'm back and plugging someone else's extension. I came across ActiveRecord Extension last night, and was quite frankly dumbfounded that 1) I hadn't come across it before, and 2) More people weren't using it. In short, it will let you use ActiveRecord to batch insert multiple rows in a single query, rather than looping over the Model#create method and getting all the overhead that comes with it (and the hundred insert statements).

Zach Dennis has written a great little plugin called ActiveRecord::Extensions which makes the bulk inserts almost as painless as your regular create.

First, install the gem:

sudo gem install ar-extensions

Include it in your app (you could put it in environment.rb if you're going to use it a lot, for now I'll place it in my model definition in user.rb:

require 'ar-extensions'
class User
end

Then to use it, it really couldn't be more straightforward. Use the following in your controller or rake task to import your data:

fields = [:first_name, :last_name, :email]
data = [["glenn", "gillen", "foo@bar.com"],
        ["john", "jones", "jim@bar.com"],
        ["steve", "smith", "bar@foo.com"]]

User.import fields, data

And we're done, 3 new rows have been inserted into the users table, with just the single query. Any more questions, the RDocs are here

Copyright © 2008, Ruby Pond Limited. All rights reserved.
powered by ruby / ruby on rails / radiant cms