Efficient multiple SQL inserts in RAILS - AR Extensions

Recently, I was required to generate multiple inserts in a RAILS project. I went with traditional way of looping through and creating AR objects.

#class definition
class CartoonCharacter < ActiveRecord::Base
end

#values for NEW objects
names = ["Tom", "Jerry", "Donald"]

#loop thru and create objects
names.each do |name|
  CartoonCharacter.create!(:name => name)
end

It's not as bigger problem if you're just creating 2 or 3 records. Imagine you want to create 70,000 records. I was required to do the same. This approach killed my machine and it just took ages. I couldn't wait after 5 minutes and killed the process.

I started looking out for other options. I came across ar-extensions.

It is a nice gem for mass import and conversion of data to CSV. It also incorporates better finder support. All I've used is its mass import functionality.

Usage:

column_names = [:name, :age, :country]
values = []

#build up values
15000.times do
 values << ["Tom", "65", "AU"]
end

#import
CartoonCharacter.import column_names, values

That's it. It worked like a charm. Yes, please don't forget to include adapter specific functionality.

require 'ar-extensions/adapters/mysql'
require 'ar-extensions/import/mysql'

Recently, some work has been done to extract the import functionality of ar-extenstions into activerecord-import.

More information available at: http://continuousthinking.com/tags/arext