Importing flat-file data in to a database with Ruby

All,

I’m up against a performance issue with some code I have - it’s on
GitHub at


if anyone wants to take a look.

The code takes a fixed 80-column wide input file, reads the first two
bytes of each line and hands off further processing to an initialize
function in a Class. An object is returned and pushed on to an Array.
When ~1000 of these records have been processed, they are INSERTed in to
a database using activerecord-import.

Without calling the import method on an ActiveRecord object, I can
process a 450Mb source file in 6-8 minutes. When writing to the
database, this easily takes in excess of an hour.

Can anyone help me work out where I’m going wrong? Both MySQL and
PostgreSQL show this ‘slowness’, so I’m looking at code first.

Kind regards,

Peter

On Tue, Aug 30, 2011 at 9:17 PM, Peter H. [email protected]
wrote:

Without calling the import method on an ActiveRecord object, I can
process a 450Mb source file in 6-8 minutes. When writing to the
database, this easily takes in excess of an hour.

What’s your benchmarking of another ORM (Sequel, DataMapper) vs.
‘handmade’ transactional SQL saying?


Phillip G.

phgaw.posterous.com | twitter.com/phgaw | gplus.to/phgaw

A method of solution is perfect if we can forsee from the start,
and even prove, that following that method we shall attain our aim.
– Leibniz

On Tue, Aug 30, 2011 at 9:17 PM, Peter H. [email protected]
wrote:

Without calling the import method on an ActiveRecord object, I can
process a 450Mb source file in 6-8 minutes. When writing to the
database, this easily takes in excess of an hour.

Can anyone help me work out where I’m going wrong? Both MySQL and
PostgreSQL show this ‘slowness’, so I’m looking at code first.

Generally OR mappers are all but ideal for mass data imports. I don’t
know AR’s import interface and how it’s implemented internally but
usually RDBMS allow for batch commands. With these you can execute
multiple SQL statements with a single network roundtrip between client
and RDBMS. When looking at your code and
https://github.com/zdennis/activerecord-import/wiki/ it seems you
might be using the interface in a sub optimal way by importing groups
of objects per class. I haven’t looked through the code extensively
but it may be that you are not processing batches of ~1000 but rather
significantly more batches of smaller number of objects making your
batch size less effective.

Not all RDBMS have proper batch interfaces (or an implementation in
Ruby that uses it) and it seems the code in
#import_without_validations_or_callbacks in
https://github.com/zdennis/activerecord-import/blob/master/lib/activerecord-import/import.rb
honors this (“if not supports_import?”) by issuing individual INSERT
statements. Which means that your batch size becomes 1. shudder

Usually best results are obtained by using a RDBMS’s batch loader
(e.g. SQL*Loder with Oracle) which have some nifty tricks to speed up
insertion. It may even be more efficient to preformat insert data for
the specific loader and either write it to a file or feed it to stdin
of the loader via a pipe in memory.

Kind regards

robert