O perl monks,
I am trying to create a module which uses DBI to work with a database. Since the connect-prepare-execute cycle was always the same, I thought I would put that part into its own subroutine, and call it when I needed to to execute SQL statements.
I'm finding that, even though I used prepare_cached, performance is as if it were preparing each statement from scratch.
O mighty humble ones, where am I screwing up? -timallen
Here is the subroutine...
sub IS4_SQL_execute {
#accept the SQL, then a list containing the bind values
my $sql = shift;
my @bind_values = @_;
my $sth; #statement handle
my $dbh; #database handle
$dbh = DBI->connect('DBI:ODBC:sybase_timallen','foo','bar')
or die "Couldn't connect to database: " . DBI->errstr;
$dbh->{LongReadLen} = 20000;
$sth = $dbh->prepare_cached($sql);
$sth->execute(@bind_values)
or die "Couldn't execute statement: " . $sth->errstr;
return ($dbh,$sth);
# I count on the calling sub finishing the statement
# and disconnecting
}
Here is an example of how I call it
my $sql = 'SELECT count(*) FROM products WHERE nr = ?';
my ($dbh,$sth) = IS4_SQL_execute($sql,$nr);
while (my @data = $sth->fetchrow_array()) {
$count = $data[0];
}
$sth->finish;
$dbh->disconnect;