Performance issues using bind and eval in DB2 accesses

Further to my recent node that asked if bind was necessary, I can now answer the question, in a limited sense, for the performance of a very simple SELECT statement: not that much at all.

I first wrote code just to do a simple SELECT from the database, then copy and pasted that code and just changed the value passing in the execute method to a bind_param call. Then I copy and pasted those two subs and wrapped everything after the database connection in an eval. I expected the eval to be quite expensive, and the bind to be fairly cheap.

#!/usr/bin/perl -w

#  Test DB2 code re bind vs. no bind and eval vs. no eval.

use strict;

use DBI;
use Benchmark qw(:all);

our $database = "db";
our $user = "user";
our $password = "password";

{

    my $results = timethese(0, {
      'noBindNoEval' => &noBindNoEval,
      'bindNoEval'   => &bindNoEval,
      'noBindEval'   => &noBindEval,
      'bindEval'     => &bindEval,
    },'none');
    cmpthese($results);
}

sub noBindNoEval {
    my $dbh = DBI->connect("DBI:DB2:$database", $user, $password );
    defined($dbh) or die "Unable to connect to database:".$DBI::errstr
+;

    my $query = "SELECT *
                   FROM table
                  WHERE name = ?
                  FOR READ ONLY";
    my $sth = $dbh->prepare($query);
    $sth->execute("abeamish") or die "Execute failed:".$DBI::errstr;
    my $result = $sth->fetchrow_hashref;

    if ( defined ( $result ) ) {

      print "Found record for $result->{'NAME'}\n";
    }

    $sth->finish;
    $dbh->disconnect;
}

sub bindNoEval {
    my $dbh = DBI->connect("DBI:DB2:$database", $user, $password );
    defined($dbh) or die "Unable to connect to database:".$DBI::errstr
+;

    my $query = "SELECT *
                   FROM table
                  WHERE name = ?
                  FOR READ ONLY";
    my $sth = $dbh->prepare($query);
    $sth->bind_param(1,"abeamish");
    $sth->execute() or die "Execute failed:".$DBI::errstr;
    my $result = $sth->fetchrow_hashref;

    if ( defined ( $result ) ) {

      print "Found record for $result->{'NAME'}\n";
    }

    $sth->finish;
    $dbh->disconnect;
}

sub noBindEval {
    my $dbh = DBI->connect("DBI:DB2:$database", $user, $password );
    defined($dbh) or die "Unable to connect to database:".$DBI::errstr
+;

    eval {
      my $query = "SELECT *
                     FROM table
                  WHERE name = ?
                  FOR READ ONLY";
      my $sth = $dbh->prepare($query);
      $sth->execute("abeamish") or die "Execute failed:".$DBI::errstr;
      my $result = $sth->fetchrow_hashref;

      if ( defined ( $result ) ) {

        print "Found record for $result->{'NAME'}\n";
      }

      $sth->finish;
    };
    if ($@) {
      print "Error encountered:".$DBI::errstr." $@";
    }
    $dbh->disconnect;
}

sub bindEval {
    my $dbh = DBI->connect("DBI:DB2:$database", $user, $password );
    defined($dbh) or die "Unable to connect to database:".$DBI::errstr
+;

    eval {
      my $query = "SELECT *
                     FROM table
                  WHERE name = ?
                  FOR READ ONLY";
      my $sth = $dbh->prepare($query);
      $sth->bind_param(1,"abeamish");
      $sth->execute() or die "Execute failed:".$DBI::errstr;
      my $result = $sth->fetchrow_hashref;

      if ( defined ( $result ) ) {

        print "Found record for $result->{'NAME'}\n";
      }

      $sth->finish;
    };
    if ($@) {
      print "Error encountered:".$DBI::errstr." $@";
    }
    $dbh->disconnect;
}
[download]

Update: Ignore this nonesense .. correct results are below, after tye's excellent response.

The results (editted to fit without wrapping) are as follows:
Rate noBndEval bndNoEval noBndNoEval bndEval noBndEval 9655605/s -- -0% -5% -5% bndNoEval 9695347/s 0% -- -4% -5% noBndNoEval 10150483/s 5% 5% -- -1% bndEval 10203485/s 6% 5% 1% --
[download]
This tells me that no binding with an eval is the slowest combination, very closely followed by binding without the eval. 5% away is neither bind nor eval, and a little ahead of that combination is both bind and eval.

Fascinating. It makes sense that bind is faster, because DBI has to do less work. And if I think about how eval might be implemented, it's possible that it finds all of the .. or die .. fragments and short-circuits them, thus stream-lining the code and making it run faster.

Well, it's not the answer I expected, but at least now I have an answer that I've determined experimentally. Next, I want to look at what kind of type checking bind_param does.

Alex / talexb / Toronto

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

Comment on Performance issues using bind and eval in DB2 accesses Select or Download Code

Replies are listed 'Best First'.
Re: Performance issues using bind and eval in DB2 accesses (noise) by tye (Sage) on May 20, 2008 at 19:52 UTC
`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	[reply] [d/l] [select]
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]
Re^3: Performance issues using bind and eval in DB2 accesses (noise) by andreas1234567 (Vicar) on May 21, 2008 at 05:39 UTC
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]