Another thing that I didn't see mentioned is that if you can't use a bulk loader (computed fields etc.), then if you can use a transaction and do the inserts then commit. If you have to use DBI to insert lots of data this will be a big time savings since the statement preparation only happens one time instead of once per record.
#... script starting stuff up here
my $dbh=DBI->connect("DBI:Pg(RaiseError=>1, AutoCommit=>0):dbname=$dat
+abase");
my $stmt="INSERT INTO table ( field1, field2 ) VALUES ( ?, ? )";
eval {
$dbh->rollback;
my $sth=$dbh->prepare($stmt);
while(<INPUT>)
{
my($field1, $field2)=split;
$sth->execute($field1, $field2);
}
$dbh->commit;
};
if($@)
{
print STDERR "Data did not insert correctly: $@";
$dbh->rollback;
}
This does 2 things: The inserts go a lot faster. The inserts go in as a group or not at all which makes cleaning up after a failure a lot easier.
The :Pg loads up a PostgreSQL connection which I used in this example since that's the database I use most.
The RaiseError=>1 turns failures in DBI calls into fatal errors which the eval traps and reports on.
The AutoCommit=>0 tells DBI not to commit records until you call commit();
-
Are you posting in the right place? Check out Where do I post X? to know for sure.
-
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big>
<blockquote> <br /> <dd>
<dl> <dt> <em> <font>
<h1> <h2> <h3> <h4>
<h5> <h6> <hr /> <i>
<li> <nbsp> <ol> <p>
<small> <strike> <strong>
<sub> <sup> <table>
<td> <th> <tr> <tt>
<u> <ul>
-
Snippets of code should be wrapped in
<code> tags not
<pre> tags. In fact, <pre>
tags should generally be avoided. If they must
be used, extreme care should be
taken to ensure that their contents do not
have long lines (<70 chars), in order to prevent
horizontal scrolling (and possible janitor
intervention).
-
Want more info? How to link
or How to display code and escape characters
are good places to start.
|