Super-fast bulk data imports in Rails with ar-extensions   (Sat Jan 19 2008)

Most Rails tutorials assume that when you import or move data it's going to be a record at a time. Yes, there are methods for importing bulk data. You can use migrations for initial data entry, and I've found FasterCSV useful for comma-delimited files.

But these are always painfully slow. Why? Because they iterate Create statements within a block. Like this:

FasterCSV.foreach("#{RAILS_ROOT}/lib/nationality.csv") do |row| Nationality.create(:nationality => row[1])
end

For each iteration, a separate SQL statement is being generated. And when you're importing thousands of records, that gives you plenty of time to go and get a coffee .. or two .. or three.

In the Accelerate HR database, we're going to be bulk-importing data frequently, notably when we capture and then interpret records from an attendance reader. Take a largish business - with a couple of thousand employees. They may clock in and out several times each day. We'll need to pick up the data once a day, and that could mean 10 - 20,000 records. Even if I ran the import in the background, that's quite a strain on the system - and delays of more than a second or two just aren't acceptable.

Well today, I solved the problem, thanks to a great piece of work from Zach Dennis. A process that was taking more than 10 minutes came down to less than 1 second.

Let me put you in the picture. I was working on the payroll setup. Each employee is assigned to a cost center, and in the Employee model we have the fields costcenter_id and costcenter_date, (this being the date when the cost center was assigned). But people may be moved from one cost center to another. The client I'm dealing with now supplies some of its staff to other businesses, so we use the cost center to determine how much to charge for these services. This means we also need a cost center history, showing all the assignments and the change-dates.

To get this started, I'd set the current cost centers for 1150 employees (using another automated process ... but that's another story.) When the user confirms that everyone's been assigned and the set-up is complete, I wanted to initialize the Costcenterhistory table sending the Employee id, costcenter_id and costcenter_date from Employee to Costcenterhistory. That's what was taking 10 minutes, using the normal looped Create method.

So I Googled around to find a better way, and came across Zach Dennis's ActiveRecord::Extensions - that's ar-extensions. (A quick heads-up here to Ilya Grigorik, who pointed me in the right direction.)

The way that Zach achieves exponential increases in speed is by adding methods to ActiveRecord that allow you to create a single SQL INSERT request instead of looping through a block. To go even faster, you can turn validation off - if you're sure that your import data has already been validated elsewhere of course.

I should add that ar-extensions isn't limited to the import feature. Zach also provides similar SQL native methods to accelerate your Finds, to benchmark your queries, and to create CSV files. You'll find all this and more in the docs.

But before I show you how it all worked for me, let me talk about the docs - and the hours of frustration trying to work it all out before I reached the wonderful Aha moment. I don't know if it's just me, but so much of the Rails documentation I come across takes me halfway there and then leaves me stranded. And Zach, I love your gem, but I wish you'd given me just a bit more help.

Installing the gem, no problem. And then right at the end of the documentation an intro to mass import/insertion of data. Open it up, and there's a description of what the gem does. I'm fine with that too. But then:

And that's it.

Now, I'm new to extensions. I've never done this before. Do I add something like this at the bottom of my Costcenterhistory model, after the last end? So I try it.

And then I guess I need to call this in my Controller:

def setup_ok
Costcenterhistory.import
end

Well it doesn't work. Ah, there's a note in Ilya's post, telling me I need to require 'ar-extensions' in my controller. So I try that.

OK, it works. Or at least the 2 records are added to the database. But there's an error message: Invalid arguments! And a reference to the line number in the gem.

And I spend the next 4-5 hours trying adding arguments to the controller reference, reading anything useful I can find on ActiveRecord extensions, working my way through Zach's code. Nothing works. So I give up. And anyway there's a match I'm planning to watch on TV.

...With two hours away from it all, the answer is blindingly obvious. You don't need to add anything to the model at all! Zach's given us a new method - Model.import - that we can use immediately in the controller. I tried it and it worked immediately.

So my plea, not just to Zach but to everyone who writes all the wonderful Rails material, is to remember that accessibility is an issue for programmers: some of us are only partially sighted and we need our instructions bold and clear. Even if it's the obvious to you, state it for us.

Rant over. Let's see how it worked out.

A. Installed the gem.
B. Added require 'ar-extensions' to the end of /config/environment.rb
C. Model - no change
D. Controller:

def setup_ok

Let's look at how I constructed the values statement.

Zach's Basic Example looks like this:
values = [ [ 'Zach', 'Dennis' ], [ 'Mark', 'Van Holstyn' ], [ 'John', 'Doe' ] ]

But clearly, with 1150 records to insert, I don't want to type in the values manually like this. Instead I use a Ruby array map to build the array of arrays. OK, it uses a block - it's iterative - but as we run through the loop we're not updating the database, only creating the SQL statement. So no performance hit.

Time to try it out. I was absolutely delighted - no flabbergasted would be a better word - when in an instant the process was completed. In fact, I didn't believe it had worked and wasn't convinced until I checked the database and found that all the records had been successfully imported. And then I had to try it again to convince myself.

Zach, thank you for a brilliant addition to my Rails tool-set. And I hope this little contribution helps to show others the way.

POSTSCRIPT

I later discovered that turning validation off during data imports - when possible - makes a huge difference to speed. Test details are here. In another post, I look at an outstanding piece of research from Nimble Method published in the last few days, helping to explain why some Rails processes can seem slow - and suggesting other fixes.

Filed under: Ruby on Rails