Dear Monks,

(CentOS 4, perl v5.8.5, DBD::Oracle 1.19, Oracle XE)

I'm relatively new to the Oracle database. "Ask Tom" seems to be a good source on efficient use of the Oracle database. For multiple reasons, he recommends (1) Stored Procedures, over (2) SQL using Bind variables, over (3) SQL not using Bind variables (Dynamic sql).

I created a test application that inserts a row, then select a row using approach (1) and (2) above with Perl and DBD::Oracle. The results surprised me: Approach (2) is consistently 20-30% faster than approach (1). 
$ ./bm_foo.pl -N=10000
Benchmark: timing 10000 iterations of insert_and_select_foo_ora_sp, in
+sert_and_select_foo_ora_sql...
insert_and_select_foo_ora_sp: 30 wallclock secs (10.17 usr +  0.38 sys
+ = 10.55 CPU) @ 947.87/s (n=10000)
insert_and_select_foo_ora_sql: 20 wallclock secs ( 5.41 usr +  0.24 sy
+s =  5.65 CPU) @ 1769.91/s (n=10000)

[download]
Running each approach individually with Devel::DProf
Approach 1 - Stored procedures: 
Total Elapsed Time = 30.88130 Seconds
  User+System Time = 10.92130 Seconds
Exclusive Times
%Time ExclSec CumulS #Calls sec/call Csec/c  Name
 42.0   4.590  5.580  20000   0.0002 0.0003  DBI::st::execute
 13.7   1.500  1.500  20000   0.0001 0.0001  DBI::st::fetchrow_array
 8.97   0.980  0.980  50000   0.0000 0.0000  DBI::st::bind_param
 8.97   0.980  7.840  10000   0.0001 0.0008  main::get_foo_ora_sp
 6.23   0.680  1.170  20000   0.0000 0.0001  DBI::st::bind_param_inout
 5.95   0.650  3.050  10000   0.0001 0.0003  main::insert_foo_ora_sp
 3.75   0.410  0.410  19998   0.0000 0.0000  DBI::common::DESTROY
 3.20   0.350  0.350  10004   0.0000 0.0000  DBI::_setup_handle
 2.47   0.270  0.990  10002   0.0000 0.0001  DBI::_new_sth
 2.47   0.270  0.710  10004   0.0000 0.0001  DBI::_new_handle
 1.74   0.190  0.190  10000   0.0000 0.0000  main::update_foo_vars
 1.74   0.190 11.270  10000   0.0000 0.0011  main::__ANON__
 0.82   0.090 11.360  10002   0.0000 0.0011  Benchmark::__ANON__
 0.82   0.090  0.090  10004   0.0000 0.0000  DBI::st::TIEHASH
 0.73   0.080  0.080   9999   0.0000 0.0000  DBD::_mem::common::DESTRO
+Y

[download]

Approach 2 - SQL using Bind variables 
Total Elapsed Time = 20.02347 Seconds
  User+System Time = 6.393479 Seconds
Exclusive Times
%Time ExclSec CumulS #Calls sec/call Csec/c  Name
 58.4   3.735  3.735  30000   0.0001 0.0001  DBI::st::execute
 15.7   1.005  1.005  30000   0.0000 0.0000  DBI::st::fetchrow_array
 10.4   0.670  3.565  10000   0.0001 0.0004  main::insert_foo_ora_sql
 8.76   0.560  2.415  10000   0.0001 0.0002  main::get_foo_ora_sql
 4.85   0.310  6.455  10000   0.0000 0.0006  main::__ANON__
 2.58   0.165  0.165  10000   0.0000 0.0000  main::update_foo_vars
 1.25   0.080  0.149      9   0.0089 0.0165  main::BEGIN
 0.55   0.035  6.490  10002   0.0000 0.0006  Benchmark::__ANON__
 0.31   0.020  0.020      4   0.0050 0.0050  DynaLoader::dl_load_file
 0.16   0.010  0.010      1   0.0100 0.0100  DBD::Oracle::db::_login
 0.16   0.010  0.010      3   0.0033 0.0033  DBD::Oracle::st::_prepare
 0.16   0.010  0.010      4   0.0025 0.0025  DynaLoader::dl_load_flags
 0.16   0.010  0.010      8   0.0012 0.0012  Pod::Parser::BEGIN
 0.16   0.010  0.020      6   0.0017 0.0033  Pod::Text::BEGIN
 0.16   0.010  0.010     23   0.0004 0.0004  vars::import

[download]
Complete perl source code: 
#!/usr/bin/perl

use strict;
use warnings;

use Devel::DProf;

use Benchmark;
use Getopt::Long;
use Pod::Usage;
use DBI;
use DBD::Oracle qw(:ora_types);

my %foo = (
  lnum1 => 123456789,
  lnum2 => 123456789,
  lnum3 => 123456789,
  lnum4 => 123456789,
);

my %sths = ();
my $dbh = undef;

##################################################################
# Insert foo using stored procedure (PL/SQL)
sub insert_foo_ora_sp {
##################################################################
  my $fooref = shift;

  # ------ prepare function call unless in cache
  if (!defined($sths{insert_foo_ora_sp})) {
    my $prepstr =
      q{BEGIN :retval := pkg_foo.func_insert_foo}.
      q{(:lnum1,:lnum2,:lnum3,:lnum4); END;};
    $sths{insert_foo_ora_sp} = $dbh->prepare ($prepstr);
  }
  if (!defined($sths{insert_foo_ora_sp})) {
    die(q{sth_prepare failed!});
  }

  my $retval = undef;
  $sths{insert_foo_ora_sp}->bind_param_inout(":retval", \$retval, 32);
  $sths{insert_foo_ora_sp}->bind_param(q{:} . $_, $fooref->{$_}) for (
+keys %{$fooref});

  # ------ execute
  my $rv = $sths{insert_foo_ora_sp}->execute();
  if ($rv != 1) {
    die(qq{sth_execute returned '$rv'});
  }

  return $retval;
}

##################################################################
# Insert foo using sql (using bound sql)
sub insert_foo_ora_sql {
##################################################################
  my $fooref = shift;

  if (!defined($sths{get_next_foo_id_sql})) {
    my $prepstr = qq{SELECT FOO_ID_SEQ.NEXTVAL FROM DUAL};
    $sths{get_next_foo_id_sql} = $dbh->prepare($prepstr);
  }
  if (!defined($sths{get_next_foo_id_sql})) {
    die(q{sth_prepare failed!});
  }

  my $rv = $sths{get_next_foo_id_sql}->execute();
  die(q{sth_execute failed:} . $!) unless $rv;

  my @arr = $sths{get_next_foo_id_sql}->fetchrow_array();


  push @arr, $foo{lnum1}, $foo{lnum2}, $foo{lnum3}, $foo{lnum4};

  if (!$sths{insert_foo_ora_sql}) {
    my $sql = "insert into foo (foo_id,lnum1,lnum2,lnum3,lnum4) values
+ (?,?,?,?,?)";
    $sths{insert_foo_ora_sql} = $dbh->prepare ($sql);
    die(q{sth_prepare failed!}) unless $sths{insert_foo_ora_sql};
  }

  $rv = $sths{insert_foo_ora_sql}->execute(@arr);
  die(q{sth_execute failed:} . $!) unless $rv;

  return $arr[0]; # return foo_id
}

##################################################################
# Get foogroup using PL/SQL stored procedure
sub get_foo_ora_sp {
##################################################################
  my $foo_id = shift;

  if (!defined($sths{get_foo_sql_sp})) {
    my $prepstr = qq{BEGIN pkg_foo.proc_get_foo (:pi_foo_id, :pio_foo 
+); END;};
    $sths{get_foo_sql_sp} = $dbh->prepare($prepstr);
  }
  if (!defined($sths{get_foo_sql_sp})) {
    die(q{sth_prepare failed!});
  }

  my $retvalsth = undef;
  $sths{get_foo_sql_sp}->bind_param(q{:pi_foo_id}, $foo_id);
  $sths{get_foo_sql_sp}->bind_param_inout(":pio_foo", \$retvalsth, 0, 
+{ ora_type => ORA_RSET } );

  my $rv = $sths{get_foo_sql_sp}->execute();
  if (!$rv) {
    die(qq{sth_execute returned '$rv'});
  }

  my $number_of_rows = 0;
  $number_of_rows++ while ( my @row = $retvalsth->fetchrow_array );

  return $number_of_rows;
}


##################################################################
# Get foo using sql
sub get_foo_ora_sql {
##################################################################
  my $foo_id = shift;

  if (!$sths{get_foo_ora_sql_foo}) {
    my $sql = "SELECT foo_id,lnum1,lnum2,lnum3,lnum4 FROM foo WHERE fo
+o_id = ?";
    $sths{get_foo_ora_sql_foo} = $dbh->prepare ($sql);
    die(q{sth_prepare failed!}) unless $sths{get_foo_ora_sql_foo};
  }

  my $rv = $sths{get_foo_ora_sql_foo}->execute($foo_id);
  die(q{sth_execute failed!}) unless $rv;

  my $number_of_rows = 0;
  $number_of_rows++ while (my @row = $sths{get_foo_ora_sql_foo}->fetch
+row_array());

  return $number_of_rows;
}

##################################################################
sub update_foo_vars {
##################################################################
  foreach my $key (keys %foo) {
    $foo{$key}++;
  }
}

# ------ main
my $N = 1e2;
my $dbistr  = q{DBI:Oracle:};
my $dbiuser = q{scott};
my $dbipass = q{tiger};

GetOptions("N|n=i" => \$N) or pod2usage(2);

$dbh = DBI->connect($dbistr, $dbiuser, $dbipass);
die(q{connection failed!}) if (!defined($dbh));

timethese($N,
          {
           insert_and_select_foo_ora_sp => sub {
             update_foo_vars();
             my $foo_id = insert_foo_ora_sp(\%foo);
             get_foo_ora_sp($foo_id);
           },
           insert_and_select_foo_ora_sql => sub {
             update_foo_vars();
             my $foo_id = insert_foo_ora_sql(\%foo);
             get_foo_ora_sql($foo_id);
           }
          }
         );

__END__

[download]
Complete PL/SQL source code: 
-- pkg_foo.sql

CREATE OR REPLACE PACKAGE pkg_foo
AS
  FUNCTION func_insert_foo
  (
        lnum1         in  NUMBER,
        lnum2         in  NUMBER,
        lnum3         in  NUMBER,
        lnum4         in  NUMBER
  ) RETURN NUMBER;

  PROCEDURE proc_get_foo
  (
        pi_foo_id     in  NUMBER,
        pio_foo       out SYS_REFCURSOR
  );

END pkg_foo;
/

CREATE OR REPLACE PACKAGE BODY pkg_foo
AS

  FUNCTION func_insert_foo
  (
        lnum1         in  NUMBER,
        lnum2         in  NUMBER,
        lnum3         in  NUMBER,
        lnum4         in  NUMBER
  ) RETURN NUMBER AS
     my_foo_id           NUMBER;
   BEGIN

     SELECT FOO_ID_SEQ.nextval
       INTO my_foo_id
       FROM dual;

     INSERT INTO foo VALUES
     (
        my_foo_id,
        lnum1,
        lnum2,
        lnum3,
        lnum4
     );

     RETURN my_foo_id;

   END func_insert_foo;

   PROCEDURE proc_get_foo
   (
         pi_foo_id         in  NUMBER,
         pio_foo           out SYS_REFCURSOR
   ) IS
   BEGIN

    OPEN pio_foo FOR
       SELECT
        foo_id,
        lnum1,
        lnum2,
        lnum3,
        lnum4
       FROM foo
      WHERE foo_id = pi_foo_id;

  END proc_get_foo;

END pkg_foo;
/

[download]
It seems it doesn't pay of using Oracle Stored Procedures while using DBD::Oracle in this particular scenario. Is the promised speed-up by using SP's lost due to the relative "cost" of binding the variables?

Comments?

Andreas
--

In reply to DBD::Oracle faster with bound sql than stored procedures? by andreas1234567

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.