Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
PerlMonks  

'DBD oddities' talk at Amsterdam.pm

by kudra (Vicar)
on Mar 09, 2001 at 22:09 UTC ( #63290=perlmeditation: print w/ replies, xml ) Need Help??

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
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) { : }

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 %

Comment on 'DBD oddities' talk at Amsterdam.pm
Select or Download Code
Re (tilly) 1: 'DBD oddities' talk at Amsterdam.pm
by tilly (Archbishop) on Mar 09, 2001 at 22:23 UTC
    A note. Performance is always a complex topic. In many environments the major bottleneck turns out to be latency. A good rant on this is here. Nominally that is about online games from home, however you would be amazed at how applicable that rant is to applications in general that talk over a network...
(Ovid - Perl6, TPJ, and other things) Re: 'DBD oddities' talk at Amsterdam.pm
by Ovid (Cardinal) on Mar 10, 2001 at 15:55 UTC
    Since I have been lounging in Amsterdam recently and rotting my brain with VBScript, I have been attemding the Amsterdam.pm meetings and have thoroughly enjoyed them. kudra covered the main points, which were fascinating, and I thought I would fill in some of the interesting "trivia" bits.

    I have, of course, finally met kudra. She's an interesting person and well worth the trouble of getting to know (of course, now she's going to kill me for all of the '/msg kudra's she'll undoubtedly receive :). ar0n was also there as was abigail. Johan Vromans is also a member, though I am not aware that he's ever been to the Monastery. After the meeting, ar0n and I caught a train back to Centraal Station in Amsterdam before he headed off to his home in Hoorn. He's further proof to me that monks in general seem to be a pretty good lot. So far, I've met 8 monks (that I'm aware of) and have come away thoroughly impressed. Go pat yourselves on the back, now :)

    Note: I asked permission before relating the following information.

    abigail mentioned that he was chatting with Nathan (Torkington, I assume) on IRC and was asking what was going on with Perl 6 and Nathan called Larry Wall who stated that Perl6 infomation will not be released immediately. Apparently, Larry wants to produce something that will last for the next 10-20 years (I can't remember the exact timeframe mentioned, but you get the idea). Since that is Larry's intent, he does not want to rush the Perl6 process. If you're holding your breath, let it out as nothing is going to happen soon.

    Also, I have good information that *something* may be happening regarding TPJ. However, I can't say what it is as it was mentioned that this might not be public knowledge. Please don't try to /msg me to find out what's up. I won't tell you. I'm just dropping that bit so that those of you (like me) who have TPJ subscriptions can know that the issue is still alive.

    Incidentally, if anyone has any tidbits of what the heck convinced Earthweb to stop publishing TPJ, I and other monks would love to know what happened. I only received one issue before they stopped publishing.

    Cheers,
    Ovid

    Join the Perlmonks Setiathome Group or just click on the the link and check out our stats.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlmeditation [id://63290]
Approved by root
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (8)
As of 2014-11-01 05:35 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    For retirement, I am banking on:










    Results (228 votes), past polls