Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot

sql loader vs perl dbi

by chuckd (Scribe)
on Jul 30, 2008 at 18:04 UTC ( #701197=perlquestion: print w/replies, xml ) Need Help??
chuckd has asked for the wisdom of the Perl Monks concerning the following question:

Hi Everyone, I need to know what my disadvantages are when using Perl DBI vs SQL Loader. I would like to use Perl DBI to input all values into my database, but everyone at work is telling me that when we have many rows to insert (200,000+) that contain sometimes lots of text in a column called text, Perl will load it really slowly compared to SQL Loader. What are my best options? I found Oracle::Sqlldr on CPAN. I'm trying to avoid writing my values to a text file to load with SQL Loader, if possible while still being able to load everything with enough speed that 500,000 rows don't take forever using Perl DBI. Any help is much appreciated.

Replies are listed 'Best First'.
Re: sql loader vs perl dbi
by Corion (Pope) on Jul 30, 2008 at 18:12 UTC

    Usually, the database vendors maintain and supply their bulk loading utility for a reason - you can't beat sqlldr (or in my case, isql) for their raw loading speed. They are able to circumvent some of the API restrictions that the rest of the database interface needs to follow.

    I recommend you look into writing the control file and then shell out to sqlldr to load your data. A templating system will make generating the control file very convenient.

    A second route could be to see whether Oracle supports something like LOAD TABLE..., that is, an SQL/DBI interface to sqlldr. That will likely only work on the machine where the database process lives, but you could get a nicer API for error reporting that way than when parsing command output.

Re: sql loader vs perl dbi
by MidLifeXis (Monsignor) on Jul 30, 2008 at 18:07 UTC

    I would concur with your workmates. DBI is very flexible, but the vendor supplied batch loading tools will typically run circles around anything written in DBI.


Re: sql loader vs perl dbi
by runrig (Abbot) on Nov 21, 2012 at 23:22 UTC
    DBD::Oracle natively supports the execute_array method, and it is quite fast. Maybe not quite as fast as sqlldr, but it should get through 200,000 records (in batches of 1000 or so) in no time. sqlldr with direct path might be the fastest, but direct path has it's own issues that should be considered (e.g. letting you insert duplicates leaving unique constraints in a disabled state). sqlldr without direct path will be somewhere in between.

    Update: Also, DBIx::BulkUtil makes sqlldr easy.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://701197]
Approved by moritz
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others exploiting the Monastery: (4)
As of 2018-05-27 16:32 GMT
Find Nodes?
    Voting Booth?