It was suggested to me that I use a database to help prune out duplicate entries (based on 12-digit id numbers) in data I'm processing. I had previously been using a hash, but it starts to choke by 55 million entries. So I fired up DBI::SQLite to give it a try. And I was shocked by what I found.

I simply must have something wrong, because inserting into an sqlite database seems to be occurring at a rate of only 5 per second for me; just writing raw to hashes was going between 11,000 and 20,000 per second. This is unbelievably, unusably slow. What is going on?

#!/usr/bin/perl
use strict;
use warnings;

use v5.16.0;
use lib 'lib';
use DBI;
use Carp 'croak';


my $dbh = db_handle('vals.db');
my $sql_table = <<"SQL";
CREATE TABLE IF NOT EXISTS data (
    klout        INTEGER
    );
SQL
    $dbh->do($sql_table);

my $sql_insert_statement = "INSERT INTO data (klout) VALUES (?)";
my $sth = $dbh->prepare($sql_insert_statement);
my $start = time;
foreach (1..1000) {
    $sth->execute($_);
}
say "Total time: ", (time - $start); # 180 seconds

sub db_handle {
    my $db_file = shift
    or croak "db_handle() requires a database name";
    no warnings 'once';
    return DBI->connect (
    "dbi:SQLite:dbname=$db_file",
    "", #no username
    "", #no password
    { RaiseError => 1, PrintError => 0, AutoCommit => 1 },
    ) or die $DBH::errstr;
}
[download]