in reply to Re^2: Ordering Template cards
in thread Ordering Template cards

I have encountered similar problems before and I'm not sure either.
Perl has some complicated rules about "definedness" and creating "my" variables within looping statements.
I hope the Monks can further explain this seeming "weirdness".

As a suggestion, I most often use fetchall_arrayref.
Something like:

my $card_ref = $cards->fetchall_arrayref;
foreach my $card (@$card_ref)
{..}

[download]
This does expand the memory used by Perl to include the entire result set. But that is fine if the result set is "small" and the memory used for that result is recycled for further use by Perl. "Small" is a relative term. Nowadays, I would consider a 10,000 line result set as "small".

Replies are listed 'Best First'.
Re^4: Ordering Template cards
by bliako (Abbot) on Jan 26, 2021 at 10:38 UTC

    I always use refs as well. My first question at an API is if it can provide me with refs. Provided that the returned data will be processed locally and immediately

[reply]
      I could be wrong about this, but in my testing, asking for an array_ref like above, causes the entire result set to be copied into Perl memory. If you undefine the array_ref or assign it another value, Perl can reuse that memory. I haven't benchmarked this, but I'm sure that other Monks have: I suspect that getting an array_ref is faster than going row by row with the exception of an enormous result set.
[reply]

        Marshall, that's a good point regarding db-operations. You are right that there is memory cost for the performance boost of using refs with this specific use-case: db-IO. I was talking more generally so probably I gave the wrong impression for this specific case. Anyway refreshing on the manual is good.

        According to fetchall_arrayref of DBI a tradeoff would be to fetchall_arrayref() in batches but still use array-refs, as

        That might be the fastest way to fetch and process lots of rows using the DBI, but it depends on the relative cost of method calls vs memory allocation.
        my $rows = []; # cache for batches of rows
while( my $row = ( shift(@$rows) || # get row from cache, or reload ca
+che:
                   shift(@{$rows=$sth->fetchall_arrayref(undef,10_000)
+||[]}) )
) {
  ...
}

        [download]

        bw, bliako

[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom