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();
[download]

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.