連続しないグループ毎に一意な昇順なIDの有効性を調べたい

is_available_sequence.pl

use strict;
use warnings;
use 5.010;
use Test::mysqld;
use Benchmark qw/timethese cmpthese/;

my $bench_time = 1000;
my $row_count  = 500000;

{
    package My::Schema::Result::Test;
    use base 'DBIx::Class';
    
    __PACKAGE__->load_components(qw/Core/);

    __PACKAGE__->table("test");
    __PACKAGE__->add_columns(
        "id" => {
            data_type => "integer",
            extra => { unsigned => 1 },
            is_auto_increment => 1,
            is_nullable => 0,
        },
        "group_id" => {
            data_type => "integer",
            extra => { unsigned => 1 },
            is_nullable => 0,
        },
        "seq_id" => {
            data_type => "integer",
            extra => { unsigned => 1 },
            is_nullable => 0,
        },
    );
    
    __PACKAGE__->set_primary_key('id');
    __PACKAGE__->add_unique_constraint('group_seq', ['group_id', 'seq_id']);

}

{
    package My::Schema;
    use base 'DBIx::Class::Schema';
    __PACKAGE__->load_classes(qw/Result::Test/);
}

my $mysqld = Test::mysqld->new(
    my_cnf => {
          'skip-networking' => '', # no TCP socket
    }
) or die $Test::mysqld::errstr;
my $schema = My::Schema->connect( $mysqld->dsn, 'root', '' );
$schema->deploy;

my $rs = $schema->resultset('Result::Test');

{
    my %gseq;
    $schema->txn_begin;
    my @row;
    for (1..$row_count) {
        my $group = int(rand(30)) + 1;
        $gseq{$group} ||= 0;
        push @row, {
            group_id => $group,
            seq_id   => ++$gseq{$group},
        };
        if (1000 <= @row) {
            $rs->populate(\@row);
            @row = ();
        }
    }
    if (0 < @row) {
        $rs->populate(\@row);
    }
    $schema->txn_commit;
}

sub get_max_by_order {
    my $group = shift;
    my $row =  $rs->search({
        seq_id => $group,
    }, {
        columns   => [ 'seq_id' ],
        order_by  => { -desc => 'seq_id' },
    })->slice(0,0)->first;
    return $row ? $row->seq_id : 0;
}

sub get_max_by_max {
    my $group = shift;
    return $rs->search({
        group_id => $group,
    })->get_column('seq_id')->max;
}

sub get_greater_equal {
    my $group = shift;
    my $seq_id = shift;
    my $row =  $rs->search({
        group_id => $group,
        seq_id   => {'>=', $seq_id}
    })->slice(0,0)->first;
    return $row ? $row->seq_id : 0;
}

my $i;

my $result = timethese($bench_time, {
    order_by => sub {
        my $seq_id = int(rand($row_count / 20));
        my $max = get_max_by_order(++$i % 30 + 1);
        return ($max < $seq_id);
    },
    max      => sub {
        my $seq_id = int(rand($row_count / 20));
        my $max = get_max_by_max(++$i % 30 + 1);
        return ($max < $seq_id);
    },
    greater_equal  => sub {
        my $seq_id = int(rand($row_count / 20));
        my $greater_equal = get_greater_equal(++$i % 30 + 1, $seq_id);
        return $greater_equal == 0;
    },
});

cmpthese $result;

max_benchmark.pl

use strict;
use warnings;
use 5.010;
use Test::mysqld;
use Benchmark qw/timethese cmpthese/;

my $bench_time = 1000;
my $row_count  = 500000;

{
    package My::Schema::Result::Test;
    use base 'DBIx::Class';
    
    __PACKAGE__->load_components(qw/Core/);

    __PACKAGE__->table("test");
    __PACKAGE__->add_columns(
        "id" => {
            data_type => "integer",
            extra => { unsigned => 1 },
            is_auto_increment => 1,
            is_nullable => 0,
        },
        "group_id" => {
            data_type => "integer",
            extra => { unsigned => 1 },
            is_nullable => 0,
        },
        "name" => {
            data_type => "varchar",
            is_nullable => 0,
            size => 64
        },
        "length" => {
            data_type => "integer",
            extra => { unsigned => 1 },
            is_nullable => 0,
        },
    );
    
    __PACKAGE__->set_primary_key('id');

    sub sqlt_deploy_hook {
        my ($self, $sqlt_table) = @_;

        $sqlt_table->add_index(
            name => 'group_length',
            fields => ['group_id', 'length'],
        );
    }
}

{
    package My::Schema;
    use base 'DBIx::Class::Schema';
    __PACKAGE__->load_classes(qw/Result::Test/);
}

my $mysqld = Test::mysqld->new(
    my_cnf => {
          'skip-networking' => '', # no TCP socket
    }
) or die $Test::mysqld::errstr;
my $schema = My::Schema->connect( $mysqld->dsn, 'root', '' );
$schema->deploy;

my $rs = $schema->resultset('Result::Test');

{
    $schema->txn_begin;
    my @row;
    for (1..$row_count) {
        push @row, {
            group_id => $_ % 30 + 1,
            name     => "test-$_",
            length   => int(rand(100000)),
        };
        if (1000 <= @row) {
            $rs->populate(\@row);
            @row = ();
        }
    }
    if (0 < @row) {
        $rs->populate(\@row);
    }
    $schema->txn_commit;
}

sub get_max_by_order {
    my $group = shift;
    return $rs->search({
        group_id => $group,
    }, {
        columns   => [ 'length' ],
        order_by  => { -desc => 'length' },
    })->slice(0,0)->first->length;
}

sub get_max_by_max {
    my $group = shift;
    return $rs->search({
        group_id => $group,
    })->get_column('length')->max;
}

say "order 1: ".get_max_by_order(1);
say "max   1: ".get_max_by_max(1);

say "order 23: ".get_max_by_order(23);
say "max   23: ".get_max_by_max(23);

my $i;

my $result = timethese($bench_time, {
    order_by => sub {
        my $max = get_max_by_order(++$i % 30 + 1);
    },
    max      => sub {
        my $max = get_max_by_max(++$i % 30 + 1);
    },
});

cmpthese $result;

連続しないグループ毎に一意な昇順なIDの有効性を調べたい

check

$perl is_available_sequence.pl

Benchmark: timing 1000 iterations of greater_equal, max, order_by...
greater_equal:  2 wallclock secs ( 2.03 usr +  0.06 sys =  2.09 CPU) @ 478.47/s (n=1000)
       max:  2 wallclock secs ( 1.98 usr +  0.05 sys =  2.03 CPU) @ 492.61/s (n=1000)
  order_by: 345 wallclock secs ( 2.52 usr +  0.07 sys =  2.59 CPU) @ 386.10/s (n=1000)
               Rate      order_by greater_equal           max
order_by      386/s            --          -19%          -22%
greater_equal 478/s           24%            --           -3% 
max           493/s           28%            3%            --

max

$perl max_benchmark.pl

Benchmark: timing 1000 iterations of max, order_by...
       max:  3 wallclock secs ( 2.05 usr +  0.05 sys =  2.10 CPU) @ 476.19/s (n=1000)
  order_by:  2 wallclock secs ( 2.24 usr +  0.06 sys =  2.30 CPU) @ 434.78/s (n=1000)
          Rate order_by      max
order_by 435/s       --      -9%
max      476/s      10%       --