CPAN::Forum
DBD-Oracle - Fetch from ref cursor VERY slow compared to SELECT
| Posted on Wed Jan 4 18:45:28 2006 by cbiswa |
| Fetch from ref cursor VERY slow compared to SELECT |
|
Hi I have seen using DBD-Oracle, that fetching rows from a REF CURSOR is very slow as compared to fetching results of a SELECT (an order of magnitude slower). I have not seen this performance issue when accessing oracle via other languages (like Java). I am listing test code. Is there a fix for this (or what am i doing wrong)? Appreciate any help. Thanks Biswa
/*************************************************
* Test results
*************************************************/
Time to fetch first row (select): [0.005545]...
Time to fetch 10000 rows (select): [0.211206].
Time to fetch first row (refcursor): [0.005663]...
Time to fetch 10000 rows (refcursor): [7.980726].
/*************************************************
* ora_setup.sql - setup test stuff in oracle
*************************************************/
-- create test table (if not present)
CREATE TABLE TMP_TEST_TBL(
TEST_COL NUMBER
);
;
-- remove existing data from test table
DELETE FROM TMP_TEST_TBL
WHERE NVL(TEST_COL,0) IS NOT NULL
;
-- insert test data
BEGIN
FOR idx IN 1..10000 LOOP
INSERT INTO TMP_TEST_TBL(TEST_COL) VALUES(idx);
END LOOP;
COMMIT;
END;
;
-- create test procedure
CREATE OR REPLACE procedure tmp_test_proc(p_result OUT SYS_REFCURSOR) AS
BEGIN
OPEN p_result FOR
SELECT *
FROM tmp_test_tbl;
END tmp_test_proc;
;
/*************************************************
* test.pl - perl script to test fetch performance
*************************************************/
#!/usr/bin/perl -w
use strict;
use DBI;
use DBD::Oracle qw(:ora_types);
use Time::HiRes qw(gettimeofday tv_interval);
##################################################
# startup
##################################################
my $dbh = DBI->connect("dbi:Oracle:MYDB", "ME", "XYZ");
$dbh->{RowCacheSize}=512;
##################################################
# execute (select)
##################################################
my $sth = $dbh->prepare('select * from tmp_test_tbl');
my $tm_start=[gettimeofday];
$sth->execute();
##################################################
# fetch (select)
##################################################
my $row_count=0;
while (my $array = $sth->fetchrow_arrayref()) {
$row_count++;
if ($row_count==1) {
my $elapsed=tv_interval($tm_start);
print "Time to fetch first row (select): [$elapsed]...\n";
}
}
my $elapsed=tv_interval($tm_start);
print "Time to fetch $row_count rows (select): [$elapsed].\n";
##################################################
# execute (refcursor)
##################################################
$sth = $dbh->prepare('BEGIN tmp_test_proc(?); END;');
my $rs;
$sth->bind_param_inout(1,\$rs,0,{ ora_type => ORA_RSET });
$tm_start=[gettimeofday];
$sth->execute();
##################################################
# fetch (refcursor)
##################################################
$row_count=0;
while (my $array = $rs->fetchrow_arrayref()) {
$row_count++;
if ($row_count==1) {
my $elapsed=tv_interval($tm_start);
print "Time to fetch first row (refcursor): [$elapsed]...\n";
}
}
$elapsed=tv_interval($tm_start);
print "Time to fetch $row_count rows (refcursor): [$elapsed].\n";
|
| Direct Responses: 5119 | Write a response |
(15)
]
