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]

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

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":



  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.