From: "Southworth, Harry" <[email protected]>
> 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";
I don't know how's your XML supposed to be structured, but could you
swith the loops around? That way you could prepare
"SELECT * FROM $theTable WHERE usubjid = ?";
in the outer loop and then just $cnx->execute($id) in the inner.
If you can't do this, prepare the statement for each table first and
then use that:
my %sth;
for (0..$#tables){
$sth{$tables[$_]} = $dbh->prepare(
"SELECT * FROM $theTable WHERE usubjid = ?"
);
}
...
while( $id = $sth->fetchrow_array() ){
for (0..$#tables){
$sth{$tables[$_]}->execute($id) ...
...
It's possible that the prepares are not the reason of the slowness.
Do you have indexes on the usubjid column in all tables?
Jenda
===== [email protected] === http://Jenda.Krynicky.cz =====
When it comes to wine, women and song, wizards are allowed
to get drunk and croon as much as they like.
-- Terry Pratchett in Sourcery
--
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
http://learn.perl.org/
