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

[iaw4]

thanks a lot, WizardOfUz.

Let's presume that I wanted to mimick the code of my berkeley data base, though, in which I first look up whether the key already exists, and then I combine values into the same key if it is already there. in this case, was my original assessment of sqlite's speed correct? that is, I first need to insert every record, so that I can look up the next record.

if this is true, then it seems to me that the berkeley data base would be a lot faster for situations in which "bulk inserts" are not possible, but sqlite is much faster for situations in which it is.

I have not installed mysql, but I wonder if someone could tell me how fast/slow the same code in mysql would be.

[pmqs]

Here is an easy way to make the bulk-update for DB_File work a lot faster -- preprocess the input data before going near the database. What you can do is very dependent on the nature of your data, but you initial example implies you will always append to existing entries in the DB. So here is an example that show the gains that can be made by preprocessing - I've assumes there are 1000 unique keys in the database, and I'm adding 50k records.

First, note the performance gain from preprocessing.

           s/iter   original preprocess
original     3.26         --       -93%
preprocess  0.221      1375%         --

And here is the code

#!/usr/bin/perl

use strict;
use warnings;

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

my $DB_FILE1;
my $DB_FILE2;


my $NUM_RECORDS = 50_000;
my $NUM_KEYS    = 1000;

setup_dbfile();

Benchmark::cmpthese(
    10,
    {
        'original' => \&benchmark_dbfile1,
        'preprocess'   => \&benchmark_dbfile2
    }
);



sub benchmark_dbfile1 {

    foreach my $value ( 1 .. $NUM_RECORDS ) {

        my $key = int(rand($NUM_KEYS));

        if ( exists $DB_FILE1->{$key} ) {
            $DB_FILE1->{$key} .= ",$value";
        }
        else {
            $DB_FILE1->{$key} = $value;
        }

    }
}

sub benchmark_dbfile2 {

    my %preprocess = ();
    foreach my $value ( 1 .. $NUM_RECORDS ) {

        my $key = int(rand($NUM_KEYS));
        push @{ $preprocess{$key} }, $value ;
    }

    while (my ($key, $val_list) = each %preprocess) {

        my $value = join ",", @$val_list;

        if ( exists $DB_FILE2->{$key} ) {
            $DB_FILE1->{$key} .= ",$value";
        }
        else {
            $DB_FILE2->{$key} = $value;
        }

    }
}

sub setup_dbfile {

  {
    unlink 'berkeley.db1';  
    my %data;
    tie %data, 'DB_File', 'berkeley.db1' or die "$!";
    $DB_FILE1 = \%data;
  }
  {
    unlink 'berkeley.db2';  
    my %data;
    tie %data, 'DB_File', 'berkeley.db2' or die "$!";
    $DB_FILE2 = \%data;
  }
}

[WizardOfUz]

Using the original key generation logic, which produces hardly any key collisions, I get the following results:

           s/iter preprocess   original
preprocess   4.30         --        -4%
original     4.11         5%         --

[pmqs]

That is hardly surprising - the test harness is carrying out more db writes plus the extra cost of preprocessing that it will never use.

We would need to know more about the input data (like the % of key colisions) to take this any further.

[pmqs]

Another point to note about the sqllite example code is that is doing a straight insert, not an update. You original code, at the start of this thread, imples you want updates. So is a bulk insert a valid use-case for your application?