H. Merijn Brand, author of DBD::Unify, gave a talk at the March Amsterdam.pm meeting that I found interesting as well as somewhat relevant to recent discussions. The following is a summary with some examples (full examples might be available on the Amsterdam.pm homepage in the future). This is being reprinted with permission. In fact, I only wrote half the post.

The most efficient way to retrieve data is with bindcolumns.
Benchmarks with both Unify and Oracle showed hashref to be the least efficient form of retrieving data, followed by array and arrayref. 

    my $sth = $sth->prepare ("select field from table");
    $sth->execute;
    $href = $sth->fetchrow_hashref;   # slowest
    @arr  = $sth->fetchrow_array;
    $aref = $sth->fetchrow_arrayref;
    my $field;
    $sth->bind_columns (\$field);
    $sth->fetch;                      # fastest

[download]
This efficiency increase comes from within DBI itself, as was demonstrated by verbose output from DBD::Unify, which received the same request regardless of the fetching method.

Selecting only the fields you want is more efficient.
In tests, fetching 1 field was much faster than fetching all 8. With Unify and bindcolumns, 8 fields were retrieved at 7737.02 records per second, whereas 1 field came back at 13980.40 records per second. For oracle the numbers were 54249.67 and 138082.63.

Also...
Merijn also discussed his his wrapper module which serves to hide the database handle which is not needed for read-only database access. The database handle is only truly needed for commit and rollback. Disconnect can be ignored without warnings by either an explicit undef of all statement handles, or implicitly by putting statement handles in a limited scope. Unsurprisingly, the module also simplifies column binding through the use of prepex, a function which prepares, binds and executes queries, so one can do: 

    my $sth = prepex (
        "select field1,  field2",
             \($field1, $field2),
        "from   table");
    while ($sth->fetch) {
        :
        }

[download]

Selected benchmarks

UNIFY
============== Benchmarks (2500 records)
Benchmark: running array, arrayref, bindcol, hashref, each for at leas
+t 30 CPU seconds...
  hashref: 41 wallclock secs (39.26 usr +  0.01 sys = 39.27 CPU) @  1.
+81/s (n= 71)
    array: 35 wallclock secs (33.68 usr +  0.01 sys = 33.69 CPU) @  2.
+29/s (n= 77)  26.52 %
 arrayref: 34 wallclock secs (33.31 usr +  0.01 sys = 33.32 CPU) @  2.
+49/s (n= 83)   8.73 %  37.57 %
  bindcol: 34 wallclock secs (33.28 usr +  0.01 sys = 33.29 CPU) @  2.
+58/s (n= 86)   3.61 %           42.54 %

============== Benchmarks (~ 200 000 records) [8 fields]
Benchmark: timing 5 iterations of array, arrayref, bindcol, hashref...
  hashref: 188 wallclock secs (183.68 usr +  0.47 sys = 184.15 CPU)  5
+155.63 rec/s
    array: 140 wallclock secs (133.01 usr +  0.53 sys = 133.54 CPU)  7
+109.56 rec/s  37.90 %
 arrayref: 127 wallclock secs (124.30 usr +  0.44 sys = 124.74 CPU)  7
+611.11 rec/s   7.05 %  47.63 %
  bindcol: 125 wallclock secs (122.22 usr +  0.49 sys = 122.71 CPU)  7
+737.02 rec/s   1.65 %           50.07 %

============== Benchmarks (~ 200 000 records) [1 field]
Benchmark: timing 5 iterations of array, arrayref, bindcol, hashref...
  hashref: 113 wallclock secs (108.99 usr +  0.48 sys = 109.47 CPU)  8
+672.79 rec/s
    array:  76 wallclock secs ( 73.85 usr +  0.47 sys =  74.32 CPU) 12
+774.60 rec/s  47.30 %
 arrayref:  73 wallclock secs ( 70.89 usr +  0.42 sys =  71.31 CPU) 13
+313.80 rec/s   4.22 %  53.51 %
  bindcol:  69 wallclock secs ( 67.44 usr +  0.47 sys =  67.91 CPU) 13
+980.40 rec/s   5.01 %           61.20 %

[download]

In reply to 'DBD oddities' talk at Amsterdam.pm by kudra

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.