Southworth, Harry wrote:
> I have a database with several tables in it, and each table contains data on
> lots of people. From one run of my program to the next, the tables could
> change, so my code needs to be fairly general.
>
> I want to pull the data for each person from each table, and create an XML
> report - one report for each person.
>
> The problem I'm having is that my code is very inefficient. Although I've
> read that putting prepare inside a loop is slow, I have failed to make
> anything else work at all (I tried putting the connections into an array, but
> either I did something wrong or that is not supposed to work).
>
> I would be very grateful if someone were able to tell me how to rewrite this
> in order to make if more efficient.
>
>
> use DBI;
> use IO::File;
> use XML::Writer;
>
> my $dbh = DBI->connect( 'DBI:SQLite:myDB' )
> or die "Couldn't connect to database: ". DBI->errstr;
> my $sth = $dbh->prepare( 'SELECT id FROM demography' )
> or die "Couldn't prepare statement: " . $dbh->errstr;
>
> my $id;
> my @tables = $dbh->tables();
>
> # Get the IDs
> $sth->execute();
>
> while( $id = $sth->fetchrow_array() ){
> # Open report for writing and add top-matter here:
> # code not shown
>
> for (0..$#tables){
> $theTable = $tables[$_];
> $qid = $dbh->quote( $id );
>
> # Do stuff to make the next line produce the correct SQL:
> # code not shown
> $sql = "SELECT * FROM $theTable WHERE usubjid = $id";
>
> # Next lines are inefficient. Please help!
> $cnx = $dbh->prepare($sql);
> $cnx->execute()
> or die "Failed execution";
>
> # Code omitted here
> } # Close while
>
> # Tidy up and close down here
I suggest that there is probably no need to extract a list of IDs from the
demography table and then access the records one at a time. The program below
gives a hint of what I mean, but I clearly can't test it for you.
use strict;
use warnings;
use DBI;
use IO::File;
use XML::Writer;
my $dbh = DBI->connect( 'DBI:SQLite:myDB' )
or die "Couldn't connect to database: ". DBI->errstr;
foreach my $theTable ($dbh->tables) {
my $sth = $dbh->prepare("SELECT * FROM $theTable ORDER BY usubjid");
while (my @data = $sth->fetchrow_array) {
# Open report file for append here according to usubjid field. If
# file doesn't exist then open for output and add top-matter.
# Keep note of all new files created.
}
}
# Add any tail data to all files created
HTH,
Rob
--
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
http://learn.perl.org/
