Re: DBD::Sponge: When malloc throws in the sponge

[Steffen Goeldner]

Attached is a small benchmark script. The test platform is:
  CPU: x86 1994 MHz
  RAM: 500 MB
  OS : Win2k
I redirected the output to circumvent the slow console:
  set ADO_DSN=Provider=MSDAORA;Data Source=prod;User ID=***;Password=***
  perl Benchmark.pl > Benchmark.out
The output looks like:
  ...
  undef, 'TST', 'PERL_DBD_TEST', 'A', undef
  undef, 'TST', 'PERL_DBD_TEST', 'B', undef
  undef, 'TST', 'PERL_DBD_TEST', 'C', undef
  undef, 'TST', 'PERL_DBD_TEST', 'D', undef
  ...
  47638 rows
  381 wallclock secs (373.38 usr +  0.66 sys = 374.03 CPU)
Let's exit the loop after the first record to see how long the
server needs for the query:
  my $row = [ map { $rs->Fields( $_ )->{Value} } 0..4 ];
  $rs->MoveNext;
  push @$rows, $row;
  last;
    4 wallclock secs ( 0.05 usr +  0.03 sys =  0.08 CPU)
Initially, I experimented with the mapping of the Fields. In addition
to the positional lookup above, we can use a hash-like lookup:
  my $row = [ map { $rs->{$_}{Value} } qw(TABLE_CATALOG TABLE_SCHEMA TABLE_NAME 
COLUMN_NAME DESCRIPTION) ];
   71 wallclock secs (67.47 usr +  0.56 sys = 68.03 CPU)
That's quite surprising! For the sake of completeness:
  my $row = [(map { $_->{Value} } Win32::OLE::in( $rs->Fields ) ) [ 0..4 ] ];
   82 wallclock secs (77.73 usr +  0.56 sys = 78.30 CPU)
Does the positional lookup takes that long? Next, we don't populate the
array:
  my $row = [ map { $rs->Fields( $_ )->{Value} } 0..4 ];
  $rs->MoveNext;
  # push @$rows, $row;
   21 wallclock secs (17.36 usr +  0.11 sys = 17.47 CPU)
It looks like 'push @$rows ...' and 'map { ...' interact in strange
ways and performance greatly varies between the 3 variants.
Finally, let's use the new subroutine approach:
  my $rows = sub {
    return if $rs->{EOF};
    my $row = [ map { $rs->Fields( $_ )->{Value} } 0..4 ];
    $rs->MoveNext;
    return $row;
  };
  24 wallclock secs (19.83 usr +  0.50 sys = 20.33 CPU)
Not only that the last approach lead to a much more moderate
memory utilization, but it also outperforms the other variants
significantly!
Steffen
use Win32::OLE();
use DBI();
use Benchmark();
$Win32::OLE::Warn = 2;
my $t0 = Benchmark->new;

my $cxn = Win32::OLE->new('ADODB.Connection');
$cxn->Open( $ENV{ADO_DSN} );
my $rs = $cxn->OpenSchema( 4, undef );  # adSchemaColumns
my $rows = [];
while ( !$rs->{EOF} ) {
  my $row = [ map { $rs->Fields( $_ )->{Value} } 0..4 ];
  $rs->MoveNext;
  push @$rows, $row;
}
DBI->connect('dbi:Sponge:')
   ->prepare('', { rows => $rows, NUM_OF_FIELDS => 5 } )
   ->dump_results;
print Benchmark::timestr( Benchmark::timediff( Benchmark->new, $t0 ) );