Home > perl, Programming > Perl DBI bind_columns vs. fetchrow_hashref bench marking

Perl DBI bind_columns vs. fetchrow_hashref bench marking

Have been wondering the real difference between bind_columns and fetchrow_hashref methods while fetching data using perl DBI and here’s the findings I’ve come up with:

[04/17/2011 10:35:53] Using bind_columns method starts …
[04/17/2011 10:36:39] Using bind_columns method ends …
[04/17/2011 10:36:39] Using fetchrow_hashref method starts …
[04/17/2011 10:38:36] Using fetchrow_hashref method ends …

The clear winner is bind_columns (47 seconds, which is much less than the time taken for fetchrow_hashref method, 1 minute and 57 seconds) The table being tested has 150,000 records and it’s structure is as follows:

describe tbl_userinfo;
+----------+------------+------+-----+---------+----------------+
| Field    | Type       | Null | Key | Default | Extra          |
+----------+------------+------+-----+---------+----------------+
| id       | int(11)    | NO   | PRI | NULL    | auto_increment |
| password | char(32)   | NO   |     | NULL    |                |
| active   | tinyint(4) | NO   |     | 1       |                |
+----------+------------+------+-----+---------+----------------+

The test is done by looping each method 10 times through the script below:

#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use Time::Local;
use POSIX qw/strftime/;

sub d {
	my $msg=shift;
	print strftime("[%m/%d/%Y %H:%M:%S] ", localtime(time)).$msg."\n";
}

sub get_records_bind {
	my ($sth, $ref_records)=@_;
	$sth->execute();
	my($id, $password, $active);
	$sth->bind_columns(undef, \$id, \$password, \$active);
	while($sth->fetch()) {
		$ref_records->{$id}={
			password=>$password,
			active=>$active,
		};
	}
}

sub get_records_hash {
	my ($sth, $ref_records)=@_;
	$sth->execute();
	while(my $ref=$sth->fetchrow_hashref()) {
		$ref_records->{$ref->{id}}={
			password=>$ref->{password},
			active=>$ref->{active}
		};
	}
}	

my $dbh=DBI->connect( "DBI:mysql:database=testdb:host=localhost", 'username', 'password') or die "Can't connect $DBI::errstr";

my $sth=$dbh->prepare("select id,password,active from tbl_userinfo");

d("Using bind_columns method starts ...");
my $r={};
for my $i (1..10) {
	%$r=();
	get_records_bind($sth, $r)
}
d("Using bind_columns method ends ...");

d("Using fetchrow_hashref method starts ...");
for my $i (1..10) {
	%$r=();
	get_records_hash($sth, $r)
}
d("Using fetchrow_hashref method ends ...");

$dbh->disconnect or warn "Disconnection failed" if $dbh;

Therefore I think it’s better to use the bind_columns method whenever possible, especially when the dataset is huge.

Advertisements
Categories: perl, Programming
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: