If I am undertanding this correctly, you have a 'not in' predicate which
will have a varying number of elements from one execution to the next,
right?
Given that, I would construct an array of statement handles something like
this:
my @sth;
while (<IN>){
my $num = scalar @arguments;
$sth[$num] = $dbh->prepare(&generate_sql($num)) unless
defined($sth[$num]);
$sth[$num]->execute(@arguments);
#do your fetching, etc.
}
This will work only if the Database and DBD module that you are using
allow multiple statement handles on the same db handle, but it will cut
down on the number of prepares you ahve to do dramatically.
If you can't have multiple statement handles on one DB handle, then you
could create a @dbh array as well.
____________________________
Jeff Seger
Fairchild Semiconductor
[EMAIL PROTECTED]
____________________________
"Sunil A.V." <[EMAIL PROTECTED]>
11/03/2004 12:47 PM
Please respond to "Sunil A.V."
To: [EMAIL PROTECTED]
cc:
Subject: Re: Re: RE: Perl Question:
Hi all,
I have put this followup question to my previous question [might have been
hidden in my previous email].
FOLLOWUP QUESTION:
I have a data file with about 40000 rows. Each row has a material name. I
will have to fetch about 10 attributes depending on this material name. So
I have 10 queries as each of them are in different tables. Among them 3
are to be fetched from a differnt db itself. So I hae 2 db connections and
10 prepared statements. Among 10 prepared statements I have 2 which are of
the above type ['not in' queries]. So I have to put the syntax for
prepared statement inside the loop of 40000. This is because I will no
about the array @ids only inside the loop.
so all in all it is taking lotof time. Its taking nearly 1.1 seconds on an
average per row after all the query optmizations I can think of. So total
time = 1.1 * 40000 which is too much for me and my app.
Problem is that I can't use callable statements [stored procs]. I have no
access/security. Any optimizations you can think of in my existing
structure?
prepare 10 db queries;
while loop through input data file
bind the variables to prepared stmt
execute the query
append the result to input data row
end while loop
Thanks,
Sunil
