[Dbix-class] prefetch across multiple tables

[Lasse Makholm]

On Wed, Dec 17, 2014 at 7:39 PM, John Stoffel <john at stoffel.org> wrote:
>
>
> Hi all,
>
> With a bit of nudging, I had a head-smack moment and upgraded to the
> latest version of DBIx::Class on CPAN, and it now looks like things
> are working better for me, but I'm still getting some interesting
> behavior.
>
> Before I got errors, now I can do:
>
>    > ../bin/dbic-test.pl Emlen
>    DBIx::Class::ResultSet::_construct_results(): Unable to properly
> collapse has_many results in iterator mode due to order criteria -
> performed an eager cursor slurp underneath. Consider using ->all() instead
> at ../bin/dbic-test.pl line 20
>

Do you have proper non-nullable primary keys set up in all the relevant
result classes?


>    Full Name: Emlen & Howell (8213)
>      account_id=5920 boxfolder_id=234  URL: b16f10
>      account_id=5921 boxfolder_id=249  URL: b17f10
>      account_id=5922 boxfolder_id=281  URL: b19f12
>    Full Name: Emlen, Caleb (8214)
>      account_id=5923 boxfolder_id=14  URL: b01f14
>      account_id=5924 boxfolder_id=50  URL: b04f05
>
>
> So I think I need to change my loop from:
>
> while (my $r = $rs->next) {
>   print "Full Name: ", $r->full_name, " (", $r->name_id, ")\n";
>
>   foreach my $a ($r->account()) {
>     print "  account_id=", $a->account_id();
>     print " boxfolder_id=",$a->boxfolder_id()," ";
>     my $t = $a->url();
>     $t =~ m/value2=(\w+)\&/;
>     print " URL: $1";
>     $vol = $a->boxfolder->volume;
>     $folder = $a->boxfolder->volume;
>     $range = $a->boxfolder->range;
>     print " V=$vol " if defined $vol;
>     print " F=$folder " if defined $folder;
>     print " R=$range " if defined $range;
>
>     print "\n";
>   }
> }
>
>
> To something more like this:
>
>    my @r = $rs->all
>    foreach my $r (@r) {
>

Be aware that this will cost you in terms of memory because you'll inflate
all row objects once. Especially if you prefetch a lot... Depending on your
specific result sets, this may not matter to you... But it's definitely
something to be aware of...

We've seen lots of worker processes gobble up 50 - 100 MB of extra memory
because of this, so we've been converting most uses of foreach my $row
($rs->all) { ... } to while (my $row = $rs->next) { ... }. YMMV.

/L


>      print "Full Name: ", $r->full_name, " (", $r->name_id, ")\n";
>
>      foreach my $a ($r->account()) {
>        print "  account_id=", $a->account_id();
>        print " boxfolder_id=",$a->boxfolder_id()," ";
>        my $t = $a->url();
>        $t =~ m/value2=(\w+)\&/;
>        print " URL: $1";
>        $vol = $a->boxfolder->volume;
>        $folder = $a->boxfolder->volume;
>        $range = $a->boxfolder->range;
>        print " V=$vol " if defined $vol;
>        print " F=$folder " if defined $folder;
>        print " R=$range " if defined $range;
>
>        print "\n";
>      }
>    }
>
> And that does seem to do the trick.  Now to move onto updating my
> Dancer web interface and see if I'm getting proper data out (or that I
> even have proper data IN!) of the DB and displayed nicely for the end
> users.
>
> Thanks for the help, I feel a little dumb for not thinking to upgrade
> right off the bat.
>
> Cheers,
> John
>
> _______________________________________________
> 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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20141217/516df16c/attachment.htm>