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.