sunil.... do me a favor... give me what you said in psuedo code... show me your logic in a rough code/logic flow..
thanks.. -bruce -----Original Message----- From: Sunil A.V. [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 03, 2004 9:47 AM To: [EMAIL PROTECTED] 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
