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
