in reply to Performance issues using bind and eval in DB2 accesses

10203485/s          6%         5%         1%           --

[download]

Given that guess at number of iterations per second and those percentages, the main thing you can conclude from these benchmark results is "these are all so equivalent in performance that meaningful comparison of them is beyond the abilities of Benchmark". Any claims as to which is fastest are simply unfounded based on those results.

Update: And based on what you are trying to test, you should have questioned those results. I suspect you are missing some backslashes and have only tested how fast constant expressions can be run. &foo doesn't give you a reference to the foo subroutine.

- tye        

Replies are listed 'Best First'.
Re^2: Performance issues using bind and eval in DB2 accesses (noise)
by talexb (Chancellor) on May 20, 2008 at 20:11 UTC

    Well, I've truly botched that. After adding backslashes to make the routines real code refs, and after commenting out the print statements, here are the new numbers: 

    -bash-3.00$ perl -w db2test1.pl 
              Rate     bndEval   bndNoEval   noBndEval noBndNoEval
bndEval     114/s          --        -20%        -30%         -30%
bndNoEval   143/s         26%          --        -11%         -11%
noBndEval   161/s         42%         13%          --          -0%
noBndNoEval 161/s         42%         13%          0%           --

    [download]
    Now the results are a) statistically relevant and b) ordered more the way I first expected, with the no binding and no eval choice running as the fastest method.

    Alex / talexb / Toronto

    "Groklaw is the open-source mentality applied to legal research" ~ Linus Torvalds

[reply]
[d/l]
      Do you mind adding a stored procedure to your benchmark? E.g. something like: 
      -- db2 -td@ -f proc_get_data.sql
CONNECT TO VIPER
@

DROP PROCEDURE PROC_GET_DATA
@

CREATE PROCEDURE PROC_GET_DATA
(
  IN  pi_name     VARCHAR(32)
)
  LANGUAGE SQL
  SPECIFIC PROC_GET_DATA
  NOT DETERMINISTIC
  NO EXTERNAL ACTION
pgd: BEGIN
  DECLARE v_name      VARCHAR(32)   DEFAULT '';

  DECLARE c_trans CURSOR WITH RETURN FOR
    SELECT * FROM TABLE WHERE NAME = v_name;
  SET v_name = pi_name;

  OPEN c_trans;

END pgd
@

COMMIT WORK
@

CONNECT RESET
@

      [download]
      You should be able to do the same comparison (bind vs. no-bind ) there: 
      use strict;
use warnings;

use Benchmark qw (cmpthese);
use DBD::DB2;
use DBD::DB2::Constants;

my $dbh = DBI->connect("DBI:DB2:viper", "db2inst1", "foobar");
defined($dbh) or die "Unable to connect to database:" . $DBI::errstr;

sub proc_bind {
  my $stmt = "CALL PROC_GET_DATA(?)";
  my $sth = $dbh->prepare($stmt) or die "prepare failed";
  $sth->bind_param(1, "abeamish");
  $sth->execute() or die "Execute failed:" . $DBI::errstr;
}

sub proc_no_bind {
  my $stmt = "CALL PROC_GET_DATA(?)";
  my $sth = $dbh->prepare($stmt) or die "prepare failed";
  $sth->execute("abeamish") or die "Execute failed:" . $DBI::errstr;
}

cmpthese(
  -1,
  {
    'proc_bind'    => sub { proc_bind },
    'proc_no_bind' => sub { proc_no_bind },
  }
);

__END__

      [download]
      The difference between the two is not measurable at my local system (note that the table had 0 rows so the numbers do not compare to yours): 
      $ perl -w 687634.pl
               Rate    proc_bind proc_no_bind
proc_bind    1569/s           --          -3%
proc_no_bind 1615/s           3%           --

      [download]
      (Having a table named table is slightly confusing, to say the least, but apparently valid in DB2 :)
      --
      No matter how great and destructive your problems may seem now, remember, you've probably only seen the tip of them. [1]
[reply]
[d/l]
[select]

In Section Meditations