DBD::SQLite might be the answer to this particular case, but it is slow when you try to insert into a table with index. (Not sure what happens without index, general speaking insert is slower with index, when select is faster with index. That's OT)
I compared DBD::SQLite, with ODBC, the same table structure and index. insert 1000 rows, and then select. It took ODBC 2 seconds to insert 1000 rows, and 0 (which means less than 1) second to select; but it took SQLite 310 seconds to insert (way to big), adn 0 second to select (which is virtually the same):
use DBI;
use Data::Dumper;
use strict;
use warnings;
my $dbh = DBI->connect("dbi:SQLite:dbname=dbfile","","");
#my $dbh = DBI->connect("dbi:ODBC:everything","","");
=document
#$dbh->do('create table table1(col1 number(10), col2 number(10))');
$dbh->do('create table table1(col1 int, col2 int)');
$dbh->do('create index index1 on table1(col1)');
#$dbh->do('create table table2(col1 number(10), col2 number(10))');
$dbh->do('create table table2(col1 int, col2 int)');
$dbh->do('create index index2 on table2(col1)');
=cut
$dbh->do('delete from table1');
$dbh->do('delete from table2');
my $st1 = $dbh->prepare('insert into table1(col1, col2) values(?, ?)')
+;
my $st2 = $dbh->prepare('insert into table2(col1, col2) values(?, ?)')
+;
print time, "\n";
for my $i (1..1000) {
$st1->execute($i, $i * 2);
if ($i % 2) {
$st2->execute($i, $i * 3);
}
}
print time, "\n";
{
my $st3 = $dbh->prepare('SELECT t1.col1 FROM table1 t1 LEFT OUTER
+JOIN table2 t2 ON (t1.col1 = t2.col1) WHERE t1.col1 IS NOT NULL AND t
+2.col1 IS NULL');
$st3->execute();
my $ref3 = $st3->fetchall_arrayref;
#print Dumper($ref3);
}
print time, "\n";
$dbh->disconnect();