Perl acceleration DBI fetchrow_hashref

Question

I have something similar to this:

my $report = new ReportGenerator; #custom object my $dbh = $dbc->prepare('SELECT * FROM some_table WHERE some_condition'); #DBI handle $dbh->execute(); while(my $href = $dbh->fetchrow_hashref){ $report->process_record($href); } $dbh->finish(); print $report->printReport(); 

My problem is that each iteration of the loop is very slow. The problem is MySQL. I was wondering if it is possible to put some kind of wrapper in a while loop so that it retrieves more than one record at a time, at the same time, fetching all records into memory is also not practical. I'm not worried about code efficiency (hashref vs arrayref, etc.). Rather, I'm interested in a sample that lets you talk 10,000 records at a time.

There are ~ 5 million records in the database. I can not change / update the server.

thanks

Answer 1

You can use the fetchall_arrayref function, which takes the argument "maxrows":

 while (my $data = $dbc->fetchall_arrayref(undef, 10000)) { for my $row( @{$data} ) { $report->process_record($row); } } 

You can also see the RowCacheSize property, which tries to control how many records are returned when retrieving from your driver.

Answer 2

Which bit is slow? Is it a call to execute , fetchrow_hashref or process_record ? It seems unlikely to me that the fetchrow_hashref problem is. Most likely, this is a query or a black box process_record .

But this is all guesswork. It is impossible to really help here. I recommend that you get some real code performance data using Devel :: NYTProf .

Answer 3

The fastest way to get strings as hashes using DBI is to use bind_columns() as follows:

  $sth->execute; my %row; $sth->bind_columns( \( @row{ @{$sth->{NAME_lc} } } )); while ($sth->fetch) { print "$row{region}: $row{sales}\n"; } 

This is only suitable if you are happy that each line reuses the same hash.

Other than that, I agree with davorg, avoid guesswork: first measure.

For more information about using DBI, including performance, see the training slides section (since 2007, but still relevant).