in reply to Re^2: Performance issues using bind and eval in DB2 accesses (noise)
in thread Performance issues using bind and eval in DB2 accesses

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 @
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__
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% --
(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]