the commitments

When I was testing BlackBerry typed-alike words (dactonyms?) I found that sqlite was averaging about 1 insert per second. This is by no means good.

It turns out that, under Perl, sqlite auto-commits after every write. This slows things down terribly. Here’s how to fix this:

When opening the database handle, turn AutoCommit off:

my $dbh =
DBI->connect( “dbi:SQLite:bberry2.sqlite”, “”, “”, { AutoCommit => 0 } )
or die “$!”;

Then, only commit occasionally — say every thousand writes:

while ( … ) {

…$id++;
$dbh->commit unless ( $id % 1000 );

}
$dbh->commit;

It works out about 1000 times quicker this way.

Send the author to the moon!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>