From: Robert Klemme <shortcutter@...>
Date: 2011-08-31T16:43:15+09:00
Subject: Re: Importing flat-file data in to a database with Ruby

On Tue, Aug 30, 2011 at 9:17 PM, Peter Hicks <peter.hicks@poggs.co.uk> wrote:
> I'm up against a performance issue with some code I have - it's on
> GitHub at
> https://github.com/poggs/tsdbexplorer/blob/master/lib/tsdbexplorer/cif.rb 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.

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

-- 
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/