Thanks for the reply. The SQL being executed is: SELECT me.mp_strand, mp_ref_id.rs_name, me.mp_start, me.mp_end, me.mp_freq FROM ngs_mappings me JOIN ngs_map_exps mp_me_id ON mp_me_id.me_id = me.mp_me_id JOIN ngs_ref_seqs mp_ref_id ON mp_ref_id.rs_id = me.mp_ref_id WHERE ( me_sample LIKE 'WT_t%_rep1' );
Which when run in an SQL client takes <1 sec to run and via 'normal' DBI the full code still runs in ~1.5 min. So, there's nothing wrong with the SQL. There seems to be something up with DBIC. This is DBIC v0.08122 BTW.
Cheers, Chris On 08/06/10 14:59, Ronald J Kimball wrote:
On Tue, Jun 8, 2010 at 9:09 AM, Chris Cole <[email protected] <mailto:[email protected]>> wrote: # 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/], } ); Add this line here: $self->storage->debug(1); to see the SQL that is being executed. You can compare that to your hard-coded SQL to see why it's so slow. while (my $hit = $rs->next()) { my $freq = $hit->get_column('mp_freq'); $nHits += $freq; } return($nHits); } Ronald
_______________________________________________ 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]
