orm_bench.pl

#!/usr/bin/env perl
use strict;
use warnings;
use Benchmark qw(cmpthese);
use DBI;
use Test::More;

{
    package DBIC::Schema::Result::User;
    use parent qw/DBIx::Class::Core/;
    __PACKAGE__->table('user');
    __PACKAGE__->add_columns(qw/id hoge/);
    __PACKAGE__->set_primary_key('id');

    package DBIC::Schema;
    use parent qw/DBIx::Class::Schema/;
    __PACKAGE__->register_class(User => 'DBIC::Schema::Result::User');
    # __PACKAGE__->load_namespaces(); doesn't work in single file
}

{
    package SKN;
    use DBIx::Skinny;

    package SKN::Schema;
    use DBIx::Skinny::Schema;
    install_table user => schema {
        pk 'id';
        columns qw/id hoge/;
    };

    package SKN::Row::User;
    use parent 'DBIx::Skinny::Row';
}

{
    package TNG;
    use parent 'Teng';
    __PACKAGE__->load_plugin('Lookup');

    package TNG::Schema;
    use Teng::Schema::Declare;
    table {
        name "user";
        pk "id";
        columns qw/id hoge/;
    };
}

{
    package Sny;

    use parent qw/DBIx::Sunny::Schema/;

    sub new {
        my $class = shift;
        require DBIx::Sunny;
        my $dbh = DBIx::Sunny->connect(@_);
        bless {dbh => $dbh}, $class;
    }

    __PACKAGE__->query(
        '_add_user_fast',
        id   => 'Int',
        hoge => 'Str',
        q{INSERT INTO user ( id, hoge ) values ( ?, ? )},
    );

    sub add_user_fast {
        my $self = shift;
        $self->_add_user_fast(@_);
        $self->last_insert_id;
    }

    sub add_user {
        my $self = shift;
        my $id = $self->add_user_fast(@_);
        $self->retrieve_user({id => $id});
    }

    __PACKAGE__->select_all(
        'user_list',
        q{SELECT id, hoge FROM user LIMIT 20}
    );

    __PACKAGE__->select_row(
        'retrieve_user',
        'id' => 'Int',
        q{SELECT id, hoge FROM user WHERE id = ?}
    );
}

my $table = 'create table user (id int primary key,hoge text)';
my @dsn = ('dbi:SQLite::memory:','','');
my $dbi = DBI->connect(@dsn);
$dbi->do($table);

my $dbic = DBIC::Schema->connect(@dsn);
$dbic->storage->dbh->do($table);

my $tng = TNG->new(dbh => DBI->connect(@dsn));
$tng->do($table);

my $sny = Sny->new(@dsn);
$sny->do($table);

my $skn = SKN->new;
$skn->set_dbh(DBI->connect(@dsn));
$skn->do($table);

our $dbi_user_id  = 1;
our $dbic_user_id = 1;
our $tng_user_id  = 1;
our $sny_user_id  = 1;
our $skn_user_id  = 1;

my $methods = [qw/insert fast_insert single lookup search search_by_sql search_named/];
my $subs = {
    'dbi' =>  {
        get_result => sub {
            my $method = shift;
            [ref $_[0] eq 'ARRAY' ? @{$_[0]} : @_]
        },
        insert => sub {
            $dbi->do('INSERT INTO user (id, hoge) VALUES(?, ?)', undef, $dbi_user_id, 'moge');
            +{id => $dbi_user_id++, hoge => 'moge'}
        },
        fast_insert => sub {
            $dbi->do('INSERT INTO user (id, hoge) VALUES(?, ?)', undef, $dbi_user_id, 'moge');
            $dbi_user_id++
        },
        single => sub {
            $dbi->selectrow_hashref('SELECT id, hoge FROM user WHERE id = ?', undef, 1);
        },
        lookup => sub {
            $dbi->selectrow_hashref('SELECT id, hoge FROM user WHERE id = ?', undef, 1);
        },
        search => sub {
            $dbi->selectall_arrayref('SELECT id, hoge FROM user LIMIT 20', +{Slice => {}});
        },
        search_by_sql => sub {
            $dbi->selectall_arrayref('SELECT id, hoge FROM user LIMIT 20', +{Slice => {}});
        },
    },
    'dbic' =>  {
        get_result => sub {
            my ($method) = shift;
            if ( $method eq 'fast_insert' ) {
                return [$_[0]];
            }
            [map {+{$_->get_columns}} (ref $_[0] eq 'ARRAY' ? @{$_[0]} : @_)]
        },
        insert => sub {
            $dbic->resultset('User')->create({ id => $dbic_user_id++, hoge => 'moge' })
        },
        fast_insert => sub {
            $dbic->resultset('User')->create({ id => $dbic_user_id++, hoge => 'moge' })->id
        },
        single => sub {
            $dbic->resultset('User')->single({id => 1});
        },
        lookup => sub {
            $dbic->resultset('User')->find(1);
        },
        search => sub {
            [$dbic->resultset('User')->search({}, { rows => 20 })]
        },
    },
    'teng' =>  {
        get_result => sub {
            my ($method) = shift;
            if ( $method eq 'fast_insert' ) {
                return [$_[0]];
            }
            [map {$_->get_columns} (ref $_[0] eq 'ARRAY' ? @{$_[0]} : @_)]
        },
        insert => sub {
            my $row = $tng->insert(user => { id => $tng_user_id++, hoge => 'moge' }); $row;
        },
        fast_insert => sub {
            $tng->fast_insert(user => { id => $tng_user_id++, hoge => 'moge' })
        },
        single => sub {
            $tng->single(user => { id => 1 } );
        },
        lookup => sub {
            $tng->lookup(user => +{ id => 1 } );
        },
        search => sub {
            [$tng->search(user => {}, { limit => 20 })]
        },
        search_by_sql => sub {
            [$tng->search_by_sql('SELECT id, hoge FROM user LIMIT 20')]
        },
        search_named => sub {
            [$tng->search_named('SELECT id, hoge FROM user where id < :id LIMIT 20', {id => 20})]
        },
    },

    'skinny' =>  {
        get_result => sub {
            my ($method) = shift;
            if ( $method eq 'fast_insert' ) {
                return [$_[0]];
            }
            [map {$_->get_columns} (ref $_[0] eq 'ARRAY' ? @{$_[0]} : @_)]
        },
        insert => sub {
            $skn->insert(user => { id => $skn_user_id++, hoge => 'moge' })
        },
        fast_insert => sub {
            $skn->insert(user => { id => $skn_user_id++, hoge => 'moge' })->id
        },
        single => sub {
            $skn->single(user => { id => 1 } );
        },
        lookup => sub {
            $skn->single(user => { id => 1 } );
        },
        search => sub {
            [$skn->search(user => {}, { limit => 20 })]
        },
        search_by_sql => sub {
            [$skn->search_by_sql('SELECT id, hoge FROM user LIMIT 20')]
        },
        search_named => sub {
            [$skn->search_named('SELECT id, hoge FROM user where id < :id LIMIT 20', {id => 20})]
        },
    },
    'sunny' => {
        get_result => sub {
            my $method = shift;
            [ref $_[0] eq 'ARRAY' ? @{$_[0]} : @_]
        },
        insert => sub {
            $sny->add_user({id => $sny_user_id++, hoge => 'moge'});
        },
        fast_insert => sub {
            $sny->add_user_fast({id => $sny_user_id++, hoge => 'moge'});
        },
        single => sub {
            $sny->retrieve_user({id => 1});
        },
        lookup => sub {
            $sny->retrieve_user({id => 1});
        },
        search => sub {
            $sny->user_list
        },
        search_by_sql => sub {
            $sny->select_all('SELECT id, hoge FROM user LIMIT 20');
        },
    }
};

for my $method (@$methods) {
    diag "$method\n";
    my $results = {};
    my $first;
    for my $orm ( keys %$subs ) {
        my $sub = $subs->{$orm}{$method};
        unless ( $sub ) {
            diag "$orm is not support $method";
            next;
        }

        $results->{$orm} = $subs->{$orm}{get_result}->($method, $sub->());
        if ( !$first ) {
            $first = $results->{$orm};
            ok $first, $orm;
        }
        else {
            is_deeply $first => $results->{$orm}, $orm;
        }
    }
}
done_testing;

print "\n";
for my $pkg (qw/DBI DBIx::Class DBIx::Skinny Teng DBIx::Sunny/) {
    no strict 'refs';
    print "$pkg: ";
    my $ver = "${pkg}::VERSION";
    print "${$ver}\n";
}

#print "DBI: $DBI::VERSION\n";
#print "DBIx::Class version: $DBIx::Class::VERSION\n";
#print "Teng version: $Teng::VERSION\n";

for my $method (@$methods) {
    print "\n--- $method ---\n";
    my $args = +{};
    for my $orm ( keys %$subs ) {
        if ( $subs->{$orm}{$method} ) {
            $args->{$orm} = $subs->{$orm}{$method};
        }
    }
    cmpthese(-1, $args);
}

__END__

result.txt

DBI: 1.631
DBIx::Class: 0.082810
DBIx::Skinny: 0.0742
Teng: 0.26
DBIx::Sunny: 0.22

--- insert ---
          Rate   dbic   teng skinny  sunny    dbi
dbic    2983/s     --   -16%   -72%   -78%   -92%
teng    3565/s    19%     --   -67%   -73%   -91%
skinny 10762/s   261%   202%     --   -19%   -72%
sunny  13307/s   346%   273%    24%     --   -65%
dbi    38399/s  1187%   977%   257%   189%     --

--- fast_insert ---
          Rate   dbic skinny   teng  sunny    dbi
dbic    2914/s     --   -69%   -72%   -84%   -92%
skinny  9294/s   219%     --   -10%   -50%   -74%
teng   10383/s   256%    12%     --   -44%   -70%
sunny  18666/s   541%   101%    80%     --   -47%
dbi    35136/s  1106%   278%   238%    88%     --

--- single ---
          Rate   dbic   teng skinny    dbi  sunny
dbic    2112/s     --   -58%   -60%   -93%   -93%
teng    5052/s   139%     --    -5%   -83%   -83%
skinny  5333/s   152%     6%     --   -82%   -82%
dbi    29277/s  1286%   480%   449%     --    -2%
sunny  29867/s  1314%   491%   460%     2%     --

--- lookup ---
          Rate   dbic skinny   teng    dbi  sunny
dbic    1253/s     --   -75%   -90%   -95%   -95%
skinny  4982/s   298%     --   -60%   -82%   -82%
teng   12329/s   884%   147%     --   -56%   -56%
dbi    27747/s  2114%   457%   125%     --    -0%
sunny  27836/s  2122%   459%   126%     0%     --

--- search ---
          Rate   dbic   teng skinny  sunny    dbi
dbic    1786/s     --   -40%   -42%   -83%   -87%
teng    2983/s    67%     --    -3%   -71%   -78%
skinny  3062/s    71%     3%     --   -70%   -78%
sunny  10259/s   474%   244%   235%     --   -25%
dbi    13766/s   671%   361%   350%    34%     --

--- search_by_sql ---
          Rate skinny   teng  sunny    dbi
skinny  4015/s     --    -3%   -59%   -76%
teng    4148/s     3%     --   -58%   -76%
sunny   9863/s   146%   138%     --   -42%
dbi    16999/s   323%   310%    72%     --

--- search_named ---
         Rate skinny   teng
skinny 3319/s     --   -16%
teng   3955/s    19%     --