[Dbix-class] prefetch too slow

[Randy Moore]

rahed wrote:
> Hi,
>
> in Catalyst I get a resultset from five tables roughly like this:
> (actualy there are more columns)
>
> $rs = $c->model('MyAppDB:Tab1')->search(undef,
>       {prefetch=> {rel1=>{rel2=>['rel3','rel4']}},
>        select  => ['me.id','me.col2',
>                    \'strftime(\'%s\',date)',
>                   ],
>        as      => [qw/id col2 col3/],
>        rows    => 1000,
>        page    = $page,
>        },
>        );
>
> Then I step through the resultset like this:
>
> while (my $row = $rs->next) {
>   some stuff ...
> }
>
> The query takes around one minute nevertheless from a command line the same
> one retrieves the data within one second.
> The time is spent within the next method.
> Is something wrong with this approach?
>
> I've lived with it by limiting the rows option to 50 but now I'd like to
> put all data (more than 10000 rows) into a file.
>
> Is there a better and more efficient way?
> Thanks much.
>       
>   
Hi,

I'm a total beginner with DBIC, but can you use 'join' instead of 
'prefetch'?

I've got a somewhat similar query structured as:

{
    page => $c->request->params->{page} || '1',
    rows => '10',
    select => [ { distinct => 'listing_id' },
                 qw/    me.merchant_id
                        me.user_id
                        me.card_id
                        me.current_price
                        me.current_discount
                        me.offers_accepted
                        merchant.name
                        merchant.merchant_id
                        owner.name
                    /
                 ],
    order_by => 'current_discount desc',
    join => [ { 'merchant' => { 'merchant_categories' => 'category'}, }, 
'owner' ],
}

As long as I've named all of the columns I need in the select, no 
additional queries get run each time I call 'next'.


-- 
Randy Moore
Axion Information Technologies, Inc.

phone: 301-587-3300 x 511
fax:   301-585-7450

http://www.axion-it.com