Hi Michael,
Thank you for your response!
Please help me understand clearly:
What does $sth1 -> execute( '' ) do? Why is it being done two times, once
without a variable and once with a varible in the while loop?
Since I am querying against 5 tables, should I do 5 seperate queries with
five prepare,execute,and fetch? Like the following you have suggested:
> my $sth0 = $dbh -> prepare( 'SELECT a, b, c FROM table1 ORDER BY 1' );
> my $sth1 = $dbh -> prepare( 'SELECT d, e FROM table2 WHERE a = ? ');
> my $sth2 = $dbh -> prepare( 'SELECT f, g, h FROM table3 WHERE d = ? ');
> my $sth3 = $dbh -> prepare( 'SELECT i, j, k FROM table4 WHERE f = ? ');
> my $sth4 = $dbh -> prepare( 'SELECT l, m, n FROM table5 WHERE j = ?' );
>
> $sth0 -> execute;
> $sth1 -> execute( '' );
> $sth2 -> execute( '' );
$sth3 -> execute( '' );
$sth4 -> execute( '' );
> $sth0 -> bind_columns( \( $sA, $sB, $sC ) );
> $sth1 -> bind_columns( \( $sD, $sE ) );
> $sth2 -> bind_columns( \( $sf, $sg, $sh ) );
$sth3 -> bind_columns( \( $si, $sj, $sk ) );
$sth4 -> bind_columns( \( $sl, $sm, $sn ) );
> # Use the fetched values
> while ( $sth0 -> fetch ) {
> $sth1 -> execute( $sA );
> while ( $sth1 -> fetch ) {
> # Do Something with $sA ... $sE
> }
>
> $sth2 -> execute( $sD );
> while ( $sth2 -> fetch ) {
> # Do Something with $sf ... $sh }
$sth3 -> execute( $sg );
> while ( $sth -> fetch ) {
> # Do Something with here
> }
}
$sth4 -> execute( $sk);
> while ( $sth4 -> fetch ) {
> # Do Something with $sl ... $sn
> }
>
> }
> $dbh -> disconnect;
>
Do you know if that if there is a performance difference if I join all 5
tables and do one prepare/execute/fetch or if If I do 5 seperate
prepare/execute/fetch.
Any help is appreciated!
Thanks!
Sumera
> ----------
> From: Michael A Chase[SMTP:[EMAIL PROTECTED]]
> Sent: Wednesday, October 10, 2001 4:44 PM
> To: Shaozab, Sumera; [EMAIL PROTECTED]
> Subject: Re: How to handle duplicate information?
>
> Prepare a query on each table, execute the primary one, and fetch its rows
> in the outer loop. As you need information from each secondary table,
> execute its query with the appropriate arguments to execute() to select
> the
> necessary rows. This should be reasonably efficient as long as the
> columns
> in the where clauses of the secondary tables are indexed.
>
> Untested example:
>
> my $dbh = DBI -> connect( "dbi:Oracle:$sInst", $sUser, $sPwd,
> { AutoCommit => 0, RaiseError => 1, PrintError => 0 } );
>
> # Pre-prepare both queries and set up returned column bindings
> my ( $sA, $sB, $sC, $sD, $sE );
> my $sth0 = $dbh -> prepare( 'SELECT a, b, c FROM table1 ORDER BY 1' );
> my $sth1 = $dbh -> prepare( 'SELECT d, e FROM table2 WHERE a = ?' );
> $sth0 -> execute;
> $sth1 -> execute( '' ); # Should be no matching rows
> $sth0 -> bind_columns( \( $sA, $sB, $sC ) );
> $sth1 -> bind_columns( \( $sD, $sE ) );
>
> # Use the fetched values
> while ( $sth0 -> fetch ) {
> $sth1 -> execute( $sA );
> while ( $sth1 -> fetch ) {
> # Do Something with $sA ... $sE
> }
> # Do Something More with $sA ... $sC plus the results of the inner loop
> }
> $dbh -> disconnect;
> --
> Mac :})
> ** I normally forward private questions to the appropriate mail list. **
> Give a hobbit a fish and he eats fish for a day.
> Give a hobbit a ring and he eats fish for an age.
> ----- Original Message -----
> From: "Shaozab, Sumera" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Wednesday, October 10, 2001 13:11
> Subject: How to handle duplicate information?
>
>
> > Hello,
> >
> > I am joing 4 tables to retrieve data from an oracle database using Perl
> DBI.
> > Once I recieve the data, I put it in xml format. Table A has 1 to many
> > relationship with table B, C, and D. Table D has one to many
> releationship
> > with table B and C. The problem is that every record I recieve I get a
> lot
> > of duplicate information and I can not use distinct since I am joining
> > multiple tables. The following is the select statement:
> >
> > Select
> >
> A.R_id,A.F_id,A.pg_cnt,A.r_date,A.name,A.addr1,A.addr2,A.addr3,A.addr4,B.r
> _n
> >
> me,B.str1,B.str2,B.city,B.state,B.zip,C.stat,C.y_date,C.x_date,D.p_num,D.a
> _n
> > um from tableA A, tableB B, tableC C, tableD D
> >
> > where A.b_code = '6'
> > and (B.FK_R_id = A.R_id and B.FK_F_id = A.F_id)
> > and (C.FK_R_id = A.R_id and C.FK_F_id = A.F_id)
> >
> >
> > and (D.FK_R_id = A.R_id and D.FK_F_id = A.F_id)
> > and D.p_num IS NOT NULL
> >
> >
> > I will get thousands of records with duplicate information. Each record
> > returned is unique, however
> > A.R_id,A.F_id,A.pg_cnt,A.r_date,A.name,A.addr1,A.addr2,A.addr3,A.addr4
> is
> > repeated for each B.r_nme or each D.p_num,D.a_num etc or
> >
> A.R_id,A.F_id,A.pg_cnt,A.r_date,A.name,A.addr1,A.addr2,A.addr3,A.addr4,B.r
> _n
> > me,B.str1,B.str2,B.city,B.state,B.zip,C.stat,C.y_date,C.x_date is
> repeated
> > for each D.p_num...
> >
> > How can I approach this problem so that for each record returned I
> ignore
> > the duplicate information and get take the unique informaton for that
> A.R_id
> > and A.F_id and take that information and put it in xml format?
> >
> > Any help is appreciated....
> >
> > Thanks!!
> > Sumera
>
>
