#!/usr/bin/perl

use strict;
use warnings;
use DBI;
use Benchmark qw( timediff timestr );

my $dbh = DB_OPEN('aim6_control','localhost','3306','root','********')
+;
my $table = "aim6_project_1.4_stats";
my ($sth, $select, $td, $t0, $t1);
my %kols = ();

print "Start our tests!\n";


# Method 1
print "\n\nMethod 1\n--------\n";
$t0 = new Benchmark;
%kols = ();
$sth = $dbh->prepare(" desc $table ");
$sth->execute();
while ( my ( $NAME, $TYPE, $NULL, $KEY, $DEFAULT, $EXTRA ) = $sth->fet
+chrow ) {
  $select = $dbh->prepare(" select id, $NAME from $table ");
  $select->execute();
  while ( my ( $kol_id, $field_value ) = $select->fetchrow ) {
    $kols{$kol_id}{$NAME} = $field_value;
  } # end-while
  $select->finish;
} # end-while
$sth->finish;
$t1 = new Benchmark;
&bm_time( timediff($t1, $t0) );
&print_sample_results(%kols);


# Method 2
print "\n\nMethod 2\n--------\n";
$t0 = new Benchmark;
%kols = ();
my @fields;
$sth = $dbh->prepare(" desc $table ");
$sth->execute();
while ( my ( $NAME, $TYPE, $NULL, $KEY, $DEFAULT, $EXTRA ) = $sth->fet
+chrow ) {
  push(@fields, $NAME);
} # end-while
$sth->finish;
$select = $dbh->prepare(" select * from $table ");
$select->execute();
while ( my ( @values ) = $select->fetchrow_array ) {
  my $count = 0;
  foreach my $value (@values) {
    $kols{$values[0]}{$fields[$count]} = $value;
    $count++;
  } # end-foreach
} # end-while
$select->finish;
$t1 = new Benchmark;
&bm_time( timediff($t1, $t0) );
&print_sample_results(%kols);


exit;

sub DB_OPEN {
  my ($db_name, $host_name, $port, $db_user, $db_pass,) = @_;
  my $database = "DBI:mysql:$db_name:$host_name:$port";
  my $dbh = DBI->connect($database,$db_user,$db_pass);
} # end-sub

sub bm_time {
  my ($bm_obj) = @_;
  print "Benchmark Time: ", sprintf( "%.3f", ( @$bm_obj[1] + @$bm_obj[
+2] ) ), " cpu seconds\n\n";
} # end-sub

sub print_sample_results {
  my (%kols) = @_;
  my $count = 0;
  print "Sample Results:\n";
  foreach my $kol_id (keys %kols) {
    print "\tKOL ID:: $kol_id\t\tKOL RANK:: $kols{$kol_id}{rank}\n";
    $count++;
    last if ($count == 6);
  } # end-foreach
} # end-sub
[download]

[rsiedl@solitare aim6]$ perl test.pl
Start our tests!


Method 1
--------
Benchmark Time: 0.120 cpu seconds

Sample Results:
        KOL ID:: 32             KOL RANK:: 26
        KOL ID:: 33             KOL RANK:: 47
        KOL ID:: 21             KOL RANK:: 22
        KOL ID:: 7              KOL RANK:: 26
        KOL ID:: 26             KOL RANK:: 13
        KOL ID:: 2              KOL RANK:: 47


Method 2
--------
Benchmark Time: 0.020 cpu seconds

Sample Results:
        KOL ID:: 32             KOL RANK:: 26
        KOL ID:: 33             KOL RANK:: 47
        KOL ID:: 21             KOL RANK:: 22
        KOL ID:: 7              KOL RANK:: 26
        KOL ID:: 26             KOL RANK:: 13
        KOL ID:: 2              KOL RANK:: 47
[download]