Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris

Re: Much slower DBI on RHEL6

by Tux (Abbot)
on Feb 06, 2014 at 16:39 UTC ( #1073725=note: print w/replies, xml ) Need Help??

in reply to Much slower DBI on RHEL6

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 Oracle -> -> 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- 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

Replies are listed 'Best First'.
Re^2: Much slower DBI on RHEL6
by Anonymous Monk on Feb 06, 2014 at 18:21 UTC
    Does Perl's OO method call really produce that much of a slowdown? (BC vs DBC is 3,3 vs 3,9 in SQLite's case.) How does that happen?
    while ($sth->fetch) { # bound columns # most of the time a negligible difference # but this is sometimes faster? while (DBI::st::fetchrow_arrayref ($sth) { # 'DBC'

    Oh, and any chance of having DBD::Firebird tested, too?

      DBD::Firebird added to the list I posted before. I can't say I was positively surprised in installation and startup. OpenSUSE had the packages readily available, so installation went smooth. service started immediate, but then the shit hits the fan: I find the quick start guide way too Windows-minded and none of the commands is intuitive. Furthermore, the DBD installation is a hell: it does not find the needed libraries of header files in what I thought were pretty default locations. The firebird (and firebird-devel) packages installs the but no (symbolic) link to Creating of a new database has no command-line-tool. All and all I am not very charmed yet.

      Enjoy, Have FUN! H.Merijn
        Yeah, sorry about that. User-friendliness does not seem to be a point of focus for the developers. But thanks!

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1073725]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (5)
As of 2017-04-29 17:57 GMT
Find Nodes?
    Voting Booth?
    I'm a fool:

    Results (533 votes). Check out past polls.