perl DBI multiple query problem
Date: 13 Aug 2001 09:22:24 -0700
Message-ID: <a741872d.0108130822.1767bacb_at_posting.google.com>
Hi,
I hope you can help me on how to extract data from the oracle database without going into so many loops. 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. Ionly 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 fields after "where" in the queries are place holders.
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!
SAS Received on Mon Aug 13 2001 - 18:22:24 CEST