Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation
 
PerlMonks  

comment on

( [id://3333]=superdoc: print w/replies, xml ) Need Help??

Triggered by some other issues that came up in this thread, I reworked my speed test for access methods. The conclusion might be that when you run your database locally, as in no network delays for databases running on another host, the speed difference between the access methods is huge. fetchrow_hashref should not be used when speed really matters and the number of records visited is relatively big (for one record you won't notice a difference, as the overhead of setting up the handle will make the real fetch work disappear in noise).

Assuming you already prepared your statement handle in $sth, DBI's access-methods are:

HR while (my $ref = $sth->fetchrow_hashref) { # use $ref->{c_base} and $ref->{base} A while (my ($c_base, $base) = $sth->fetchrow_array) { # use $c_base and $base AR while (my $ref = $sth->fetchrow_arrayref) { # use $ref->[0] and $ref->[1] DAR while (my $ref = DBI::st::fetchrow_arrayref ($sth)) { # use $ref->[0] and $ref->[1] BC $sth->bind_columns (\my ($c_base, \$base)); while ($sth->fetch) { # use $c_base and $base DBC $sth->bind_columns (\my ($c_base, \$base)); while (DBI::st::fetchrow_arrayref ($sth)) { # use $c_base and $base

When measured against relatively actual installations of the databases, the results are (relative speed, higher is better):

perl-5.18.2-64int-ld perl-5.16.2-64all-ld DBI-1.631 DBI-1.631 DBD::Oracle-1.68 DBD::Oracle-1.68 Oracle 12.1.0.1.0 Oracle 11.2.0.3.0 -> 11.2.0.3.0 -> 11.2.0.3.0 HR 639 HR 387 DBC 804 A 1000 AR 913 AR 1142 BC 956 DBC 1186 DAR 967 DAR 1201 A 999 BC 1250 DBD::SQLite-1.40 DBD::SQLite-1.40 HR 255 HR 250 A 1000 A 1000 AR 1041 AR 1172 DAR 1166 DAR 1244 BC 1333 BC 1250 DBC 1455 DBC 1313 DBD::Pg-3.0.0 DBD::Pg-3.0.0 PostgreSQL 9.3.2 PostgreSQL 9.2.4 HR 198 HR 179 A 1000 A 1000 AR 1143 AR 1279 DAR 1186 DAR 1281 DBC 1326 BC 1417 BC 1332 DBC 1485 DBD::<span style="background:gold">CSV</span>-0.41 DBD::<spa +n style="background:gold">CSV</span>-0.41 HR 683 HR 791 A 1000 A 1000 AR 1164 BC 1153 DAR 1171 AR 1169 BC 1181 DAR 1176 DBC 1185 DBC 1186 DBD::mysql-4.025 DBD::mysql-4.026 MariaDB-5.5.33 MariaDB-5.5.33 HR 102 HR 95 A 999 A 1000 AR 1126 BC 1245 DAR 1271 AR 1438 BC 1287 DAR 1509 DBC 1328 DBC 1612 DBD::Firebird-1.16 firebird-2.5.2.26539 HR 565 DAR 991 AR 997 A 1000 DBC 1005 BC 1179

As you can see, BC wins over HR by a factor way over 10 on MySQL. YMMV.


Enjoy, Have FUN! H.Merijn
code

In reply to Re: Much slower DBI on RHEL6 by Tux
in thread Much slower DBI on RHEL6 by MPM

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



  • 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.
Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others learning in the Monastery: (6)
As of 2024-03-19 09:15 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found