Re^2: Efficient search through a huge dataset

[pg]

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();