Posted by: danielharan | June 12, 2007

Ruby / ActiveRecord is too slow

I just got my hands on a postal code file. First, we cut out other provinces we don’t need:

grep '^"[GHJ]' POSTALCODEWORLD-CA-GOLD.CSV >> quebec.csv

My first strategy was to use ActiveRecord to create the records. The console code looked like this:

>> post_codes.each_line do |line|
?> fields = line.split(",")
>> PostCode.create(:postal_code => fields[0],...,
:street_to_suffix => fields[21])
>> end

I was too lazy to write all that myself, the create was generated. However, the performance was an issue. There are 300k lines in that file, and less than 4k were being entered per minute in the database. From MySQL’s command line, this is what my solution looked like:

LOAD DATA LOCAL INFILE '/Users/daniel/projects/[top_secret]/quebec.csv'
-> into table post_codes
-> FIELDS TERMINATED BY ','
-> ENCLOSED BY '"'
-> LINES TERMINATED BY '\r\n'
-> (postal_code,...,
street_to_suffix)
Query OK, 301571 rows affected, 7866 warnings (10.70 sec)
Records: 301571 Deleted: 0 Skipped: 0 Warnings: 0

From > 4500 seconds to 10.70 seconds. Ruby and ActiveRecord can be incredibly slow. And I don’t care, because it’s pretty and easy to use. When I do need execution speed, I’ll use a shortcut; otherwise, my happiness and code writing speed are more important.

Advertisements

Responses

  1. Ooookay, so what’s the point ?

    Basicaly, you say that mysql is faster than mysql plus a database abstraction layer over it. Of course it is ! ActiveRecord is not optimized to do that. And by the way, ActiveRecord has to be compatible with many databases other than mysql so implementing that kind of stuff can’t be done easily.

    If all you need to do is to insert 300,000 records in a table then you don’t need ActiveRecord.

  2. ar extensions gives something on these lines [?]

  3. You can make it even faster by callin DISABLE KEYS before loading. Indexes will be created just after calling ENABLE KEYS and not while loading data, which can be a showstopper.

    Use my little gem if you want to http://github.com/jsuchal/activerecord-fast-import/tree

  4. Everything is very open with a precise explanation of the
    issues. It was really informative. Your site is extremely helpful.
    Thanks for sharing!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: