[Dbix-class] Why is this so slow?

[Chris Cole]

Given the schema provided at the end, why is this code so slow?

sub getCounts ($) {
    my $self = shift;
    my $dataset = shift;

    croak "ERROR - dataset name is required\n" unless ($dataset);

    my $nHits;

    # make SQL search both technical replicates at the same time
    $dataset =~ s/t\d/t%/;
    my $rs = $self->resultset('NgsMappings')->search(
       {
          'me_sample' => {'like', $dataset},
       },
       {
          columns => [qw/mp_strand mp_ref_id.rs_name mp_start mp_end 
mp_freq/],
          join => [qw/mp_me_id mp_ref_id/],
       }
    );

    while (my $hit = $rs->next()) {
       my $freq = $hit->get_column('mp_freq');
       $nHits += $freq;
    }
    return($nHits);
}

The equivalent SQL via DBI runs in ~1.5 min, but the above I killed 
after 30min. The DBI code is:
my $sth = $dbh->prepare("SELECT mp_strand,rs_name,mp_start,mp_end,mp_freq
                               FROM ngs_mappings
                               JOIN ngs_map_exps on mp_me_id = me_id
                               JOIN ngs_ref_seqs on mp_ref_id = rs_id
                               WHERE me_sample like ?
                            ") or die "ERROR - prepare() statement 
failed: ", $dbh->errstr();
$sth->execute($sample) or die "ERROR - execute() statement failed: ", 
$dbh->errstr;
while (my $row = $sth->fetchrow_arrayref()) {
    $nHits += $row->[4];
}
$sth->finish();

Any pointers appreciated.
Cheers,

Chris

SCHEMATA
package DB::Schema::NgsMappings;

use strict;
use warnings;

use base 'DBIx::Class';

__PACKAGE__->load_components("Core");
__PACKAGE__->table("ngs_mappings");
__PACKAGE__->add_columns(
   "mp_me_id",
   { data_type => "NUMBER", default_value => undef, is_nullable => 0, 
size => 38 },
   "mp_seq_id",
   { data_type => "NUMBER", default_value => undef, is_nullable => 0, 
size => 38 },
   "mp_ref_id",
   { data_type => "NUMBER", default_value => undef, is_nullable => 0, 
size => 38 },
   "mp_strand",
   { data_type => "NUMBER", default_value => undef, is_nullable => 0, 
size => 38 },
   "mp_start",
   { data_type => "NUMBER", default_value => undef, is_nullable => 0, 
size => 38 },
   "mp_end",
   { data_type => "NUMBER", default_value => undef, is_nullable => 0, 
size => 38 },
   "mp_cigar",
   {
     data_type => "VARCHAR2",
     default_value => undef,
     is_nullable => 1,
     size => 255,
   },
   "mp_score",
   { data_type => "NUMBER", default_value => undef, is_nullable => 1, 
size => 38 },
   "mp_rank",
   { data_type => "NUMBER", default_value => undef, is_nullable => 1, 
size => 38 },
   "mp_freq",
   { data_type => "NUMBER", default_value => undef, is_nullable => 1, 
size => 38 },
);
__PACKAGE__->set_primary_key(
   "mp_me_id",
   "mp_seq_id",
   "mp_ref_id",
   "mp_strand",
   "mp_start",
   "mp_end",
);
__PACKAGE__->belongs_to("mp_seq_id", "DB::Schema::NgsSeqs", { sq_id => 
"mp_seq_id" });
__PACKAGE__->belongs_to(
   "mp_ref_id",
   "DB::Schema::NgsRefSeqs",
   { rs_id => "mp_ref_id" },
);
__PACKAGE__->belongs_to("mp_me_id", "DB::Schema::NgsMapExps", { me_id => 
"mp_me_id" });

package DB::Schema::NgsMapExps;

use strict;
use warnings;

use base 'DBIx::Class';

__PACKAGE__->load_components("Core");
__PACKAGE__->table("ngs_map_exps");
__PACKAGE__->add_columns(
   "me_id",
   { data_type => "NUMBER", default_value => undef, is_nullable => 0, 
size => 38 },
   "me_exp_id",
   { data_type => "NUMBER", default_value => undef, is_nullable => 1, 
size => 38 },
   "me_sample",
   {
     data_type => "VARCHAR2",
     default_value => undef,
     is_nullable => 1,
     size => 255,
   },
   "me_description",
   {
     data_type => "VARCHAR2",
     default_value => undef,
     is_nullable => 0,
     size => 255,
   },
   "me_program",
   {
     data_type => "VARCHAR2",
     default_value => undef,
     is_nullable => 1,
     size => 255,
   },
   "me_version",
   {
     data_type => "VARCHAR2",
     default_value => undef,
     is_nullable => 1,
     size => 255,
   },
   "me_arguments",
   {
     data_type => "VARCHAR2",
     default_value => undef,
     is_nullable => 1,
     size => 1000,
   },
   "me_ref_db_id",
   { data_type => "NUMBER", default_value => undef, is_nullable => 1, 
size => 38 },
);
__PACKAGE__->set_primary_key("me_id");
__PACKAGE__->has_many(
   "ngs_mappings",
   "DB::Schema::NgsMappings",
   { "foreign.mp_me_id" => "self.me_id" },
);
__PACKAGE__->belongs_to(
   "me_ref_db_id",
   "DB::Schema::NgsRefDb",
   { db_id => "me_ref_db_id" },
);

package DB::Schema::NgsRefSeqs;

use strict;
use warnings;

use base 'DBIx::Class';

__PACKAGE__->load_components("Core");
__PACKAGE__->table("ngs_ref_seqs");
__PACKAGE__->add_columns(
   "rs_id",
   { data_type => "NUMBER", default_value => undef, is_nullable => 0, 
size => 38 },
   "rs_db_id",
   { data_type => "NUMBER", default_value => undef, is_nullable => 1, 
size => 38 },
   "rs_sq_len",
   { data_type => "NUMBER", default_value => undef, is_nullable => 1, 
size => 38 },
   "rs_type",
   {
     data_type => "VARCHAR2",
     default_value => undef,
     is_nullable => 1,
     size => 255,
   },
   "rs_name",
   {
     data_type => "VARCHAR2",
     default_value => undef,
     is_nullable => 1,
     size => 255,
   },
   "rs_alt_name",
   {
     data_type => "VARCHAR2",
     default_value => undef,
     is_nullable => 1,
     size => 255,
   },
   "rs_sq_no",
   { data_type => "NUMBER", default_value => undef, is_nullable => 1, 
size => 38 },
);
__PACKAGE__->set_primary_key("rs_id");
__PACKAGE__->has_many(
   "ngs_mappings",
   "DB::Schema::NgsMappings",
   { "foreign.mp_ref_id" => "self.rs_id" },
);
__PACKAGE__->belongs_to("rs_db_id", "DB::Schema::NgsRefDb", { db_id => 
"rs_db_id" });