$ ./bm_foo.pl -N=10000
Benchmark: timing 10000 iterations of insert_and_select_foo_ora_sp, insert_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 sys = 5.65 CPU) @ 1769.91/s (n=10000)
####
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::DESTROY
##
##
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
##
##
#!/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 foo_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}->fetchrow_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__
##
##
-- 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;
/