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]

---

---

• Are you posting in the right place? Check out Where do I post X? to know for sure.
• Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:

  <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>

• Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
• Want more info? How to link or How to display code and escape characters are good places to start.