I (once again) stand corrected.
from perldoc DBI:
"prepare_cached"
$sth = $dbh->prepare_cached($statement)
$sth = $dbh->prepare_cached($statement, \%attr)
$sth = $dbh->prepare_cached($statement, \%attr, $if_active)
Like "prepare" except that the statement handle returned will
be stored in a hash associated with the
$dbh. If another call is made to "prepare_cached" with the same
$statement and %attr values, then the cor-
responding cached $sth will be returned without contacting the
database server.
But if the above is true, then why is the prepare/execute/fetch segment of
his code taking as long as it is? 1.1 seconds per iteration roughly. (Per
his earlier email).
Maybe instead of too many prepares, the problem is too many executes.
Perhaps this is a case where using a single query to fetch all the rows
from the table that might match something from the file and loading them
into a hash would work better. It's hard to know without knowing the
particulars of the table/file/task.
Sunil: How many rows are in the table, and how many distinct values could
be in the @ids array?
Is there some way to filter the table (an 'active' flag or a date time
stamp perhaps) to only get those rows that might match an ID from the
file?
____________________________
Jeff Seger
Fairchild Semiconductor
[EMAIL PROTECTED]
____________________________
"CAMPBELL, BRIAN D (BRIAN)" <[EMAIL PROTECTED]>
11/04/2004 01:31 PM
To: Jeffrey Seger/Corporate/[EMAIL PROTECTED], "Sunil A.V."
<[EMAIL PROTECTED]>
cc: [EMAIL PROTECTED]
Subject: RE: Re: Re: RE: Perl Question: Optimization
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;
}
