Tested with Tomas this morning, a few notes:

using the correct relationships for prefetch: item,patron
that reduces DB calls, otherwise we are still doing a hit every time we use related object
that still doesn't increase performance

Using DBIC directly to avoid Koha objects does not change things, we still see much great performance without prefetch

Using SQL directly is faster, as expected, fetching 500 rows it was about ~1.5 seconds vs ~3 seconds with Koha:Objects, no prefetch

Can confirm, and the issue is not with Koha::Objects because using DBIC directly shows the same problem:

# 163..212 is just the range of itemnumbers I had in my issues table

time perl -e 'use Koha::Database; my $schema = Koha::Database->schema; for (163..212) { my $issue = $schema->resultset("Issue")->find({itemnumber => $_}, {prefetch => ["item", "patron"]}); my $info = $issue->item->barcode . $issue->patron->firstname }'

perl -e   1,55s user 0,06s system 97% cpu 1,646 total


time perl -e 'use Koha::Database; my $schema = Koha::Database->schema; for (163..212) { my $issue = $schema->resultset("Issue")->find({itemnumber => $_}, {prefetch => []}); my $info = $issue->item->barcode . $issue->patron->firstname }' 

perl -e   1,04s user 0,06s system 96% cpu 1,138 total

I ran both commands multiple times and had the same times

It's weird, as I have seen some improvements in other areas by using prefetch (see bug 30004)

I just wrote a benchmark for this:

#!/usr/bin/env perl

use strict;
use warnings;

use Benchmark qw/cmpthese timethese/;
use Koha::Libraries;
use Koha::Database;

my $schema = Koha::Database->schema;

cmpthese(
    -10,
    {
        kohaPrefetch => sub {
            my $checkouts = Koha::Checkouts->search({},{ prefetch => [ 'item', 'patron' ]});
            while ( my $checkout = $checkouts->next ) {
                my $item_id = $checkout->item->itemnumber;
                my $patron_id = $checkout->patron->borrowernumber;
            }
        },
        kohaJoin => sub {
            my $checkouts = Koha::Checkouts->search({});
            while ( my $checkout = $checkouts->next ) {
                my $item_id = $checkout->item->itemnumber;
                my $patron_id = $checkout->patron->borrowernumber;
            }
        },
        dbicPrefetch => sub {
            my $checkouts = $schema->resultset('Issue')->search({},{ prefetch => [ 'item', 'patron' ]});
            while ( my $checkout = $checkouts->next ) {
                my $item_id = $checkout->item->itemnumber;
                my $patron_id = $checkout->patron->borrowernumber;
            }
        },
        dbicJoin => sub {
            my $checkouts = $schema->resultset('Issue')->search({});
            while ( my $checkout = $checkouts->next ) {
                my $item_id = $checkout->item->itemnumber;
                my $patron_id = $checkout->patron->borrowernumber;
            }
        }
    }
);

It suggests prefetch is quicker in my findings.. but it's testing at a slightly different level.. I'm testing one prefetch over a large resultset.. you were testing lots of prefetches over a single result... I'll write a benchmark for that too.. 

I think it's object instantiation that's at fault here..  you are effectively instantiating the object for each iteration whereas I'm instantiating one object for all results.

In short.. it depends on how we code as to whether adding prefetch is a good idea or not.

               Rate     kohaJoin     dbicJoin kohaPrefetch dbicPrefetch
kohaJoin     1.69/s           --          -4%         -79%         -81%
dbicJoin     1.76/s           4%           --         -78%         -80%
kohaPrefetch 8.09/s         378%         359%           --         -10%
dbicPrefetch 9.03/s         434%         413%          12%           --

Result from above.. shows dbicPrefetch is fastest (I didn't measure straight SQL with and without a join).. and kohaPrefetch isn't as much slower than dbic than I thought it might be.

#!/usr/bin/env perl

use strict;
use warnings;

use Benchmark qw/cmpthese timethese/;
use Koha::Libraries;
use Koha::Database;

my $schema = Koha::Database->schema;

cmpthese(
    -100,
    {
        kohaPrefetch => sub {
            for ( my $i = 0 ; $i < 250 ; $i++ ) {
                my $checkout = Koha::Checkouts->find( { itemnumber => 10 },
                    { prefetch => [ 'item', 'patron' ] } );
                my $item_id   = $checkout->item->itemnumber;
                my $patron_id = $checkout->patron->borrowernumber;
            }
        },
        kohaJoin => sub {
            for ( my $i = 0 ; $i < 250 ; $i++ ) {
                my $checkout  = Koha::Checkouts->find( { itemnumber => 10 } );
                my $item_id   = $checkout->item->itemnumber;
                my $patron_id = $checkout->patron->borrowernumber;
            }
        },
        dbicPrefetch => sub {
            for ( my $i = 0 ; $i < 250 ; $i++ ) {
                my $checkout =
                  $schema->resultset('Issue')->find( { itemnumber => 10 },
                    { prefetch => [ 'item', 'patron' ] } );
                my $item_id   = $checkout->item->itemnumber;
                my $patron_id = $checkout->patron->borrowernumber;
            }
        },
        dbicJoin => sub {
            for ( my $i = 0 ; $i < 250 ; $i++ ) {

                my $checkout =
                  $schema->resultset('Issue')->find( { itemnumber => 10 } );
                my $item_id   = $checkout->item->itemnumber;
                my $patron_id = $checkout->patron->borrowernumber;
            }
        }
    }
);

Alternatively, for instantiation speed.. the prefetch creates a bigger, heavier, object and so clearly will be slower than without.

s/iter dbicPrefetch kohaPrefetch     kohaJoin     dbicJoin
dbicPrefetch   4.51           --          -2%         -68%         -68%
kohaPrefetch   4.42           2%           --         -67%         -68%
kohaJoin       1.45         211%         205%           --          -2%
dbicJoin       1.43         217%         210%           2%           --

Results are interesting there.. I would have expected kohaPrefetch to be slower than dbicPrefetch....

But still.. clearly instantiating multiple small objects is faster than instantiating a large object lots of times.

I think we can close this one - essentialy:
Prefetch for groups of objects that you are going to iterate over
Do not prefetch for individual objects, especially inside a loop :-)