perl DBI multiple query problem

From: SAS <sumera.shaozab_at_lmco.com>
Date: 13 Aug 2001 09:22:24 -0700
Message-ID: <a741872d.0108130822.1767bacb@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. 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 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 - 11:22:24 CDT