Jeffrey, I don't think the statement below is correct. Did you think that prepare_cached just remembers the most recently used statement handle?
I believe that prepare_cached retains all prepared statements against the db handle, not just the last one. So order shouldn't matter. Your suggested algorithm still requires prepare_cached to fish out the appropriate statement handle, if it was previously prepared, most recent or not. Whether assigning to array element or a scalar isn't making any difference. If your intent was to cache the various statement handles yourself, the code needs to be tweaked, and you'd pretty much be doing the work of prepare_cached anyway. -----Original Message---- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, November 04, 2004 10:21 AM To: Sunil A.V. Cc: [EMAIL PROTECTED] Subject: Re: Re: Re: RE: Perl Question: Optimization Ok, Here is where I think you are doing unnecessary prepares: ### Prepare an SQL statement for execution $db_sth9 = $db_dbh2->prepare_cached($db_sql_stmt9); $db_sth9->execute("$matl",@ids); $db_sth9->bind_columns( undef,\$attrib2); while ($db_sth9->fetch() ) { print "attrib2 :".$attrib2; } Even though you are using prepare_cached, every time the length of the @ids array changes, the statement gets re-prepared, so unless you have the file sorted by the number of attributes, you could be doing as many as 40000 prepares. Not good. I think you'll find that instead of using the same statement handle, creating an array of them with one for each length of the @ids is going to save you time. Something like this: ### Prepare an SQL statement for execution my $n = scalar (@ids); $db_sth[$n] = $db_dbh2->prepare_cached($db_sql_stmt9); $db_sth[$n]->execute("$matl",@ids); $db_sth[$n]->bind_columns( undef,\$attrib2); while ($db_sth[$n]->fetch() ) { print "attrib2 :".$attrib2; }
