[Dbix-class] Why is this so slow?

["Alex J. G. Burzyński"]

Hi Chris,

On 08/06/10 16:27, Chris Cole wrote:
> On 08/06/10 15:05, "Alex J. G. Burzyński" wrote:
>> Hi,
>>
>> On 08/06/10 14:09, Chris Cole wrote:
>>> Given the schema provided at the end, why is this code so slow?
>>>
>>
>>
>> As you are creating an object $hit (NgsMappings with it's relationships
>> - NgsMapExps&  NgsRefSeqs) for each row - and if you have a huge number
>> of rows returned it increases the time.
>
> How is this different from the DBI example which iterates over all
> rows with the $sth->fetchrow_arrayref()? I don't see how the DBIC
> version should be slower.
>

fetchrow_arrayref returns plain array reference, while DBIC creates
objects (DBIx::Class::Row) and as Nigel already mentioned please test if
your perl doesn't suffer from redhat bug (patched bless).

And I've just seen your response - are you able to limit the number of
rows returned to let say a hundred and run your code again with
Devel::NYTProf?

That should locate the bottleneck.


> BTW the query should return ~4m rows and the NgsMappings table has
> ~111m rows.
>
>> But if this is real-life code, there's no need to iterate over each row
>> just to sum up mp_freq, just let your DB to do the job:
>
> This isn't real-life code. I require all the columns I specify in the
> SELECT, I just simplified it here.

That's fine.

Have you tried to use DBIx::Class::ResultClass::HashRefInflator?
http://search.cpan.org/~frew/DBIx-Class-0.08122/lib/DBIx/Class/ResultClass/HashRefInflator.pm


Cheers,
Alex


> Cheers,
>
> Chris
>
> _______________________________________________
> 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/dbix-class@lists.scsys.co.uk