[Dbix-class] Why is this so slow?

[Chris Cole]

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 <chris at compbio.dundee.ac.uk
> <mailto:chris at compbio.dundee.ac.uk>> 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