With properly indexed and analyzed tables, table size should
not adversely affect joins. I join all the time against large
tables (millions of rows). For the biggest tables/most complex
queries, there's often overhead up front as the query is prepared
and executed. But then fetches are fast.
I'd start looking on the Oracle side. Write your queries as you'd
like them with table joins and optimize on the Oracle side with
that as your starting point.
For large result sets, I've also gotten some performance boosts
using the DBI RowCacheSize attribute. See the DBI and DBD::Oracle
docs. for details.
Also, what versions of DBI/Oracle are you using? That can make a
difference both at the DBI/DBD level and on the Oracle side.
----
Steve Sapovits
Global Sports Interactive
Work Email: [EMAIL PROTECTED]
Home Email: [EMAIL PROTECTED]
Work Phone: 610-491-7087
Cell: 610-574-7706
Pager: 877-239-4003
> -----Original Message-----
> From: Shaozab, Sumera [SMTP:[EMAIL PROTECTED]]
> Sent: Monday, August 13, 2001 12:03 PM
> To: [EMAIL PROTECTED]
> Subject: Multiple query problem...
>
> 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
> > > }
> > > }
> >
