[Dbix-class] Why is this so slow?

Tue, 08 Jun 2010 06:17:12 -0700 

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" }); 





_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/[email protected]

Reply via email to