Speeding up Ruby on Rails’ ActiveRecord INSERT rate
A project that I’m working on (OK, it’s TSDBExplorer) generates a metric shedload of database rows. For a record that says “This train runs between 01-01-2011 and 31-05-2011 on Mondays – Fridays”, the code generates a timetable for each day. It takes an age to import, and I hope it’s going to be fantastically quick at querying data.
There’s a big downside with ActiveRecord out-of-the-box – it takes a long time to INSERT a record in to a MySQL database. I left some INSERTs going at 9.50am, and they’d just about finished when I got back from the gym five hours later. 1.2 million rows in five hours is shockingly poor.
activerecord-import appears to solve the problem in the least impact way. To group up your INSERTs, you create new instances of a model object – say, Association. You push these in to an array, and then use the new ‘import’ method to do a mass INSERT.
I am quite happy at 62 minutes to insert 1.2 million rows, including processing, considering it’s an activity that only needs to be done twice, maybe three times a year.