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
You should be able to do the same comparison (bind vs. no-bind ) there:-- 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 @
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):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__
(Having a table named table is slightly confusing, to say the least, but apparently valid in DB2 :)$ perl -w 687634.pl Rate proc_bind proc_no_bind proc_bind 1569/s -- -3% proc_no_bind 1615/s 3% --
|
|---|