Rails bulk insert at the speed of light!

Posted on October 06, 2008

For a project I’m working on I needed to bulk insert a large amount of data into a MySql table from a rails app.

I usually rely on the ar-extensions plugin’s import feature but this time I really needed to be as quick as possible so I started looking for alternatives.

Given that I’m using MySql and that it provides a pretty good LOAD DATA INFILE statement, it seemed pretty stupid not to use it.

That’s why I’m intoducing a brand new Rails plugin that allows you to bulk insert records at the speed of light: import_with_load_data_in_file.

You can find it on Github:

http://github.com/paolodona/import_with_load_data_in_file

1. How to install it?

$ cd vendor/plugins
$ git clone git://github.com/paolodona/import_with_load_data_in_file.git

2. How to use it?

  • include the ImportWithLoadDataInFile module in your AR model, say MyModel.
  • call the MyModel.import_with_load_data_infile(cols, vals) method, as you would with ar-extensions import.

3. Example

# Table name: users
#
# id :integer(11) not null, primary key
# name :string(20)
# surname :string(32)
#
class User < ActiveRecord::Base
  # you need to include this module
  include ImportWithLoadDataInFile
end

cols = [:name, :surname]
vals = [["paolo", "dona"], ["james", "dean"]]
User.import_with_load_data_infile(cols, vals)

4. Disclaimer

This works only on MySql if the “LOAD DATA LOCAL INFILE” is enabled on both the client and the server. See the page Security Issues with LOAD DATA LOCAL for more info.

5. Benchmarks

Unofficial benchmarks say it’s up to 30% faster than ar-extensions import. There is still room for improvements as I could use unix named pipes instead of a physical file and string escapings could be cached.

Tagged with:       |