Hello,
I am having some problems with my program running very slow and I hope you
can help me figure out what I may be doing wrong:
I am using DBI ver 1.14 and DBD-Oracle ver 1.06. I am extracting some
data from our database which has around 2.7 million records. The program
would be running for hours and still never gets completed. If I start it in
the morning, it is still running the next day. Please see a snippet of my
program below which will explain how I am extracting the data. As I am
getting the data, I am putting it in an xml format using XML::writer module.
Even when I don't do any xml writing, the program is still very slow. Does
it normally take a very long time to extract 2.7 million records or my
program has some faults or maybe oracle needs tuning? Any help you can give
is very much appreciated.
Thanks!
Sumera
As you can see, I am using bind and place holders and doing the prepares
outside the loops....
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
---
#!perl
use DBI;
&connect_database;
&get_ASG;
sub connect_database
{
$user = "user01";
$password = "user_test";
$dsn = "dbi:Oracle:csgd";
$dbh = DBI ->connect($dsn, $user, $password, {RaiseError =>
1} );
}
sub get_ASG
{
$sql_ASG = qq{select br_cd, rop_num, flg_num,
to_char(rcd_dt,'yyyymmdd'), pg_num, addr_nm, adr_line1, adr_line2,
adr_line3, adr_line4 from tble_main where br_cd = '1' };
$sql_pty = q{select past_num, form_num from pty_prop where
fk_rop_num = ? AND fk_flg_num = ? };
$sth_PROP = $dbh->prepare($sql_pty);
$sql_iss = q{select to_char(iss_date,'yyyymmdd') from
tble_bil where form_num = ? AND iss_date < sysdate};
$sth_ISS = $dbh->prepare( $sql_iss);
$sql_AE = q{Select rcv_nm, str_line1, str_line2, city_nm,
ste_cd, ps_cd from tble_aesn where fk_rop_num = ?
AND fk_flg_num = ?};
$sth_AE = $dbh->prepare($sql_AE);
$sql_AR = q{Select cnv_nm, to_char(exe_dt,'yyyymmdd'),
to_char(ack_dt,'yyyymmdd') from tble_ars where
fk_rop_num = ? AND fk_flg_num = ?};
$sth_AR = $dbh->prepare( $sql_AR);
eval {
$sth = $dbh->prepare( $sql_ASG);
$sth->execute;
$sth->bind_columns(\$br_cd, \$rop_num, \$flg_num, \$rcd_dt,
\$pg_num, \$addr_nm, \$adr_line1, \$adr_line2,
\$adr_line3, \$adr_line4 );
while ($sth->fetch) {
&check_pty;
# do xml stuff here.....
}
} # close while loop
}; # close eval loop
END { $^W = 0; }
$sth->finish;
$dbh->disconnect;
}
sub check_pty {
$sth_PROP->execute($rop_num, $flg_num);
$sth_PROP->bind_columns(\$past_num,\$form_num);
while ($sth_PROP->fetch){
if ($fk_rop ne "" && $past_num ne ""){
&check_iss;
}
}
}
sub check_iss {
$sth_ISS->execute($form_num);
$sth_ISS->bind_columns(\$iss_date);
while ($sth_ISS->fetch){
&get_asgee;
&get_asgr;
# do xml stuf here
}
}
sub get_assgee
{
$sth_AE->execute($rop_num,$flg_num);
$sth_AE->bind_columns(\$rcv_nm, \$str_line1, \$str_line2,
\$city_nm, \$ste_cd, \$ps_cd);
while ($sth_AE->fetch){
# do xml stuff here
}
sub get_asgr
{
$sth_AR->execute($rop_num,$flg_num);
$sth_AR->bind_columns(\$cnv_nm, \$exe_dt, \$ack_dt);
while ($sth_AR->fetch){
# do xml stuff here
}
}
