use DBI;
use Benchmark;
use strict;
use warnings;

my $dsn = "dbi:mysql:database";
my $dbh = DBI->connect("$dsn",'user','password', {
                        RaiseError => 1,

            }
            );
            
my $statement = qq~SELECT field1,
 field2,
 field3 FROM table~;         

my $key_field = 'table_id';

timethese( 1_000, { 
    array_way => sub { 
          $ary_ref  = $dbh->selectall_arrayref($statement)
          },
    hash_way  => sub { 
          $hash_ref = $dbh->selectall_hashref($statement, $key_field)
          }
                } );

##</code><code>##

# is passed two items, 
# 1 - the data results in either a hash ref or array ref
# 2 - the ignore list as an anonymous array or hash
# 
# Print statements are commented for benchmarking
#

sub show_all_except {
    my ($dr,$ignore) = @_;
    my $output;
    if (ref($ignore) eq 'ARRAY') {
        for my $row (@{$dr}) {
            
            ROW: for my $count (0..@{$row}) {
                for (0..@{$ignore}) { 
                    if ($count == $_) {
                        next ROW;
                    }
                }
                $output .= "$row->[$count] ";
            }
            $output .= "\n";
        }
    } elsif(ref($ignore) eq 'HASH') {
        for my $row (%{$dr}) {
            for (keys %{$dr->{$row}}) {
                next if $ignore->{$_};
                $output .= "$_ = $dr->{$row}{$_} ";
            }
            $output .= "\n";
        }
    }
    return $output;
}

##</code><code>##

show_these($hash_ref,[ field3, field2, field1 ]);

sub show_these {
    my ($dr,$show) = @_;
    my $output;
    for my $row (keys %{$dr}) {
        for (@{$show}) {
            next if !$dr->{$row}{$_};
            $output .= qq!$_ = $dr->{$row}{$_} !;
        }
        $output .= "\n";
    }        
    return $output;
}

##</code><code>##

my $ary_ref;
my $hash_ref;
my $ary_out;
my $hash_out;

   timethese( 1_000, { 
    array_way => sub { 
          if (!$ary_ref) {
            $ary_ref  = $dbh->selectall_arrayref($statement);
            $ary_out = show_all_except($ary_ref,[0]);
          };
          print $ary_out;
          },
    hash_way  => sub { 
          if (!$hash_ref) {
            $hash_ref = $dbh->selectall_hashref($statement, $key_field);
            $hash_out = show_these($hash_ref,[ 'field1','field2','field3'  ]);
          };
          print $hash_out;
          }
            } );

##</code><code>##

sub show_these {
    my ($dr,$show,$sort) = @_;
    my $output;
    if (!$sort) { $sort = sub {
        my $id = shift;
        return sort { $a <=> $b } keys %{$dr}
                }
    }
    my (@order) = $sort->($dr);
    for my $row (@order) {
        for (@{$show}) {
            next if !$dr->{$row}{$_};
            $output .= qq!$_ = $dr->{$row}{$_} !;
        }
        $output .= "\n";
    }        
    return $output;
}

##</code><code>##

    my $sort =     
    sub { 
        my $rhash = shift;
        return sort { 
            $rhash->{$a}{'field3'} cmp $rhash->{$b}{'field3'} 
            } keys %{$rhash}
    };
    $hash_out = show_these($hash_ref,[ 'field1','field2','field3'  ],$sort);