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