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% --
In reply to Re^3: Performance issues using bind and eval in DB2 accesses (noise)
by andreas1234567
in thread Performance issues using bind and eval in DB2 accesses
by talexb
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |