Importing flat-file data in to a database with Ruby

dubstep · August 30, 2011, 9:19pm

All,

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

poggs/tsdbexplorer/blob/master/lib/tsdbexplorer/cif.rb

#
#  This file is part of TSDBExplorer.
#
#  TSDBExplorer is free software: you can redistribute it and/or modify it
#  under the terms of the GNU General Public License as published by the
#  Free Software Foundation, either version 3 of the License, or (at your
#  option) any later version.
#
#  TSDBExplorer is distributed in the hope that it will be useful, but
#  WITHOUT ANY WARRANTY; without even the implied warranty of
#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General
#  Public License for more details.
#
#  You should have received a copy of the GNU General Public License along
#  with TSDBExplorer.  If not, see <http://www.gnu.org/licenses/>.
#
#  $Id$
#

require 'tsdbexplorer/cif/classes.rb'

This file has been truncated. show original

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

tekhne · August 30, 2011, 10:43pm

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

tekhne · August 31, 2011, 9:43am

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
Home · zdennis/activerecord-import Wiki · GitHub 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

github.com

zdennis/activerecord-import/blob/master/lib/activerecord-import/import.rb

# frozen_string_literal: true

require "ostruct"

module ActiveRecord::Import::ConnectionAdapters; end

module ActiveRecord::Import # :nodoc:
  Result = Struct.new(:failed_instances, :num_inserts, :ids, :results)

  module ImportSupport # :nodoc:
    def supports_import? # :nodoc:
      true
    end
  end

  module OnDuplicateKeyUpdateSupport # :nodoc:
    def supports_on_duplicate_key_update? # :nodoc:
      true
    end
  end

This file has been truncated. show original

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