Quest for efficient usage of Data::Model

benchmark_plugin_authentication_store_datamodel.pl

#!/usr/local/bin/perl
# Quest for efficient usage of Data::Model - get by iterator vs. by list (vs. lookup)

use strict;
use warnings;

use Benchmark qw(cmpthese timethese);
use File::Temp;

my $db  = "testdatabase_datamodel";
my $dsn = "dbi:SQLite:dbname=$db";
END { unlink $db }

{
    package T1::Schema::Column;
    use Data::Model::Schema sugar => 't1';

    column_sugar 'user.id'
        => integer => {
            require => 1,
        };
    column_sugar 'user.username'
        => text => {
            require => 1,
        };
    column_sugar 'user.password'
        => text => {
            require => 1,
        };
}

{
    package T1::Schema::Table;
    use base qw/Data::Model/;
    use Data::Model::Schema sugar => 't1';
    use Data::Model::Driver::DBI;
    my $driver = Data::Model::Driver::DBI->new(
        dsn => $dsn,
    );
    base_driver( $driver );

    install_model user => schema {
        key 'id';
        column 'user.id' => { auto_increment => 1 };
        column 'user.username';
        column 'user.password';
    };

    if (! -f $db) {
        # create Database
        my $dbh = DBI->connect($dsn)
            or die DBI->errstr;
        foreach my $sql (__PACKAGE__->as_sqls) {
            $dbh->do($sql);
        }

        $dbh->do(<<'...');
INSERT INTO user (username, password) values ('user1', 'pass1');
...


        $dbh->do(<<'...');
INSERT INTO user (username, password) values ('user2', 'pass2');
...

        $dbh->disconnect;
    }
}

my $iteration = shift;
$iteration ||= 10000;

my $model = T1::Schema::Table->new;

cmpthese( timethese( $iteration, {
    iterator        => sub {
        return $model->get(user => {
            where => [ id => 1 ],
        })->next;
    },
    iterator_safely => sub {
        my $iterator = $model->get(user => {
            where => [ id => 1 ],
        });
        if ($iterator) {
            return $iterator->next;
        }
    },
    list            => sub {
        return ( $model->get(user => {
            where => [ id => 1 ],
            limit => 1,
        }) )[0];
    },
    list_safely     => sub {
        my @rows = $model->get(user => {
            where => [ id => 1 ],
            limit => 1,
        });
        if (@rows) {
            return $rows[0];
        }
    },
    lookup          => sub {
        return $model->lookup(user => 1);
    },
} ) );

__END__

Intel Core2 6300 @ 1.86GHz, 1GB RAM, SATA HDD (7200rpm, 8BM cache)

Benchmark: timing 10000 iterations of iterator, iterator_safely, list, list_safely, lookup...
  iterator:  6 wallclock secs ( 5.13 usr +  0.23 sys =  5.36 CPU) @ 1866.02/s (n=10000)
iterator_safely:  5 wallclock secs ( 5.09 usr +  0.27 sys =  5.36 CPU) @ 1866.02/s (n=10000)
      list:  5 wallclock secs ( 5.03 usr +  0.23 sys =  5.27 CPU) @ 1898.97/s (n=10000)
list_safely:  6 wallclock secs ( 5.09 usr +  0.19 sys =  5.28 CPU) @ 1893.94/s (n=10000)
    lookup:  4 wallclock secs ( 3.69 usr +  0.23 sys =  3.92 CPU) @ 2550.37/s (n=10000)
                  Rate iterator_safely  iterator list_safely      list    lookup
iterator_safely 1866/s              --        0%         -1%       -2%      -27%
iterator        1866/s              0%        --         -1%       -2%      -27%
list_safely     1894/s              1%        1%          --       -0%      -26%
list            1899/s              2%        2%          0%        --      -26%
lookup          2550/s             37%       37%         35%       34%        --


AMD Opteron 252 @ 2.6GHz * 2, 4GB RAM, Software RAM disk

Benchmark: timing 10000 iterations of iterator, iterator_safely, list, list_safely, lookup...
  iterator:  4 wallclock secs ( 4.05 usr +  0.27 sys =  4.31 CPU) @ 2318.57/s (n=10000)
iterator_safely:  5 wallclock secs ( 4.08 usr +  0.19 sys =  4.27 CPU) @ 2344.12/s (n=10000)
      list:  4 wallclock secs ( 4.02 usr +  0.19 sys =  4.20 CPU) @ 2379.82/s (n=10000)
list_safely:  4 wallclock secs ( 4.06 usr +  0.16 sys =  4.22 CPU) @ 2370.23/s (n=10000)
    lookup:  3 wallclock secs ( 3.09 usr +  0.11 sys =  3.20 CPU) @ 3121.10/s (n=10000)
                  Rate  iterator iterator_safely list_safely      list    lookup
iterator        2319/s        --             -1%         -2%       -3%      -26%
iterator_safely 2344/s        1%              --         -1%       -2%      -25%
list_safely     2370/s        2%              1%          --       -0%      -24%
list            2380/s        3%              2%          0%        --      -24%
lookup          3121/s       35%             33%         32%       31%        --