Hi,
I need some help on how to extract data from the oracle database without
going into so many loops pointed out by Mr. Tim Bunce from my previous
email. Because I execute, bind, and fetch in loops, my data extract is
extremly slow. I can not do any joins because the tables are extremly
large(total database has 3 million records) and one table is located in
another database. Because each query needs data from another query, I am
very clueless on how to speed up data extract. I would very much appreciate
it, If someone can help me make the extract more efficient...Any example
would be very helpful.
I have total of 5 queries and each are depended upon one or another:
$sth_A = $dbh->prepare(q{Select field1, field2 from tableA where field3 =
'6'});
$sth_A->execute;
$sth_A->bind_columns(\$field1,\$field2);
$sth_B = $dbh->prepare( q{Select field1, field2 from tableB where field3 =
tableA.field1 and field4 = tabalA.field2});
$sth_C = $dbh->prepare( q{Select field1, field2 from tableC where field1 =
tableB.field1} ); (tableC is located on another database. I only have a link
to it)
$sth_D = $dbh->prepare( q{Select field1, field2 from tableD where field3 =
tableA.field1 and field4 = tabalA.field2});
$sth_E = $dbh->prepare( q{Select field1, field2 from tableE where field3 =
tableA.field1 and field4 = tabalA.field2})
(The bold words are placeholders(?,?))
while (sth_A->fetch) {
printf "$field1,$field2\n";
$sth_B->execute($field1,$field2);
$sth_B->bind_columns(\$field3,\$field4);
while(sth_B->fetch){
printf "$field3,$field4\n";
}
$sth_C->execute($field3);
$sth_C->bind_columns(\$field5,\$field6);
while(sth_C->fetch){
printf "$field5,$field6\n";
}
$sth_D->execute($field1,field2);
$sth_D->bind_columns(\$field7,\$field8);
while(sth_D->fetch){
printf "$field7,$field8\n";
}
$sth_E->execute($field1,field2);
$sth_E->bind_columns(\$field9,\$field10);
while(sth_E->fetch){
printf "$field9,$field10\n";
}
}
Thanks in Advance!
Sumera
> ----------
> From: Tim Bunce[SMTP:[EMAIL PROTECTED]]
> Sent: Sunday, August 05, 2001 5:53 PM
> To: Shaozab, Sumera
> Cc: [EMAIL PROTECTED]
> Subject: Re: Program slow even with bind/placeholders....
>
> Nesting
> execute
> fetch
> execute
> fetch
> execute
> fetch
> execute
> fetch
>
> loops to the degree you have is spectacularly inefficient. As you've
> discovered. It's not a DBI issue - it's an application design issue.
>
> Tim.
>
> On Fri, Aug 03, 2001 at 01:36:04PM -0400, Shaozab, Sumera wrote:
> > 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
> > }
> > }
>
