Re^2: fast simple DB (sqlite?) skeleton?

yes. I added a

$dbh->do( "PRAGMA synchronous=OFF" );
[download]

at the start (just after connecting), but it still ain't too fast. maybe I added it in the wrong spot, or something else went wrong?!?

I should add that I can estimate how big my data base will be---is there a way to tell SQLite to preallocate?

would dbm be better and faster in this sort of application? (I am on OSX.)

/iaw

Comment on Re^2: fast simple DB (sqlite?) skeleton? Download Code

Replies are listed 'Best First'.
Re^3: fast simple DB (sqlite?) skeleton? by WizardOfUz (Friar) on Jan 25, 2010 at 18:52 UTC
This doesn't look too bad: #!/usr/bin/perl use strict; use warnings; use Benchmark ':hireswallclock'; use DBI (); my $DBH; setup_db(); Benchmark::timethis( 1, \&db_benchmark ); sub db_benchmark { my $sth = $DBH->prepare( 'INSERT INTO benchmark ( id ) VALUES ( ? +)' ); $DBH->begin_work; my $i = 0; while ( $i < 1_000_000 ) { $sth->execute( $i ); $i++; } $DBH->commit; return; } sub setup_db { $DBH = DBI->connect( "dbi:SQLite:dbname=benchmark.sqlite", "", "", { 'RaiseError' => 1 } ); if ( ! $DBH->tables( undef, undef, 'benchmark', 'TABLE' ) ) { $DBH->do( 'CREATE TABLE benchmark ( id INTEGER )' ); } return; } [download] Results: `#1 timethis 1: 10.4524 wallclock secs ( 9.35 usr + 0.16 sys = 9.51 C +PU) @ 0.11/s (n=1) #2 timethis 1: 9.9961 wallclock secs ( 9.31 usr + 0.18 sys = 9.49 CP +U) @ 0.11/s (n=1) #3 timethis 1: 9.85224 wallclock secs ( 9.29 usr + 0.17 sys = 9.46 C +PU) @ 0.11/s (n=1)` [download] System: AMD Athlon(tm) Dual Core Processor 4850e, HDD WD10EADS	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re^3: fast simple DB (sqlite?) skeleton?
by WizardOfUz (Friar) on Jan 25, 2010 at 18:52 UTC

This doesn't look too bad:

#!/usr/bin/perl

use strict;
use warnings;

use Benchmark ':hireswallclock';
use DBI ();

my $DBH;

setup_db();

Benchmark::timethis( 1, \&db_benchmark );

sub db_benchmark {

    my $sth = $DBH->prepare( 'INSERT INTO benchmark ( id ) VALUES ( ? 
+)' );

    $DBH->begin_work;

    my $i = 0;
    while ( $i < 1_000_000 ) {
        $sth->execute( $i );
        $i++;
    }

    $DBH->commit;

    return;

}

sub setup_db {

    $DBH = DBI->connect(
        "dbi:SQLite:dbname=benchmark.sqlite",
        "",
        "",
        {
            'RaiseError' => 1
        }
    );

    if ( ! $DBH->tables( undef, undef, 'benchmark', 'TABLE' ) ) {
        $DBH->do( 'CREATE TABLE benchmark ( id INTEGER )' ); 
    }

    return;

}
[download]

Results:

#1 timethis 1: 10.4524 wallclock secs ( 9.35 usr +  0.16 sys =  9.51 C
+PU) @  0.11/s (n=1)
#2 timethis 1: 9.9961 wallclock secs ( 9.31 usr +  0.18 sys =  9.49 CP
+U) @  0.11/s (n=1)
#3 timethis 1: 9.85224 wallclock secs ( 9.29 usr +  0.17 sys =  9.46 C
+PU) @  0.11/s (n=1)
[download]

System: AMD Athlon(tm) Dual Core Processor 4850e, HDD WD10EADS

[reply]
[d/l]
[select]