zeno has asked for the wisdom of the Perl Monks concerning the following question:

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
}

[download]
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;

[download]

Replies are listed 'Best First'.
Re: DBI performance problem
by tadman (Prior) on Jan 24, 2001 at 19:00 UTC
    Why are you using DBI->connect() inside your function? If anything is slowing down your code, that would likely be it. You should really connect() only once, as you really only need one connection do use the database.

    What I would suggest is separating your connect sequence from your function along the lines of something like this: 
    my ($dbh);

sub Connect
{
     $dbh = DBI->connect('DBI:ODBC:sybase_timallen','foo','bar')
       or die "Couldn't connect to database: " . DBI->errstr;
     $dbh->{LongReadLen} = 20000;
}

sub IS4_SQL_execute
{
    #accept the SQL, then a list containing the bind values
    my $sql = shift;
    my @bind_values = @_;
    my $sth; #statement handle
    $sth = $dbh->prepare_cached($sql);
    $sth->execute(@bind_values)
      or die "Couldn't execute statement: " . $sth->errstr;
    return ($sth);
    # I count on the calling sub finishing the statement
    # and disconnecting
}

sub Disconnect
{
    $dbh->disconnect();
}

# -- Do your stuff now

Connect();

my $sql = 'SELECT count(*) FROM products WHERE nr = ?';

my ($sth) = IS4_SQL_execute($sql,$nr);

while (my @data = $sth->fetchrow_array())
{
    $count = $data[0];
}
$sth->finish();

Disconnect();

    [download]
    If you are feeling more ambitious, put these functions in a library that exported all the functions and the '$dbh' database handle. Exporter makes this really easy.

    However, had you considered using some of the "advanced" features of DBI which can do a lot of the work for you? Here is some code that retrieves the column count in one statement, albeit a long one:      my ($count) = ${$dbh->selectcol_arrayref ("SELECT count(*) FROM products WHERE nr = ?",{},$nr)}[0]; The "select(all|row|col)_*" statements are really amazing, but potentially dangerous. If the selectcol_arrayref() function does not return a valid ARRAY reference, the statement will blow up on you, perhaps fatally, so be careful.

    Note: The '{}' in the statement represents the "\%attr" parameter. If you leave it out, DBI gets confused about finding a scalar where it expected a HASH-ref.

    You would certainly convert this into a sub if you used it more than once to simplify readability.
[reply]
[d/l]
[select]
      There might be a clearer/cleaner way to get a single column back from DBI, using the built in DBI::selectrow_array "utility" method: 
      my $statement= q{
  SELECT COUNT(*) 
    FROM products 
   WHERE nr = ?
};

my $count = $dbh->selectrow_array($statement, {}, $nr);

      [download]

      At first glance, you're probably thinking that this returns an entire array, and will just set $count to 1 no matter what (because you're expecting it to return a one element array). Though, after further inspection of DBI.pm, you'll see that this method checks wantarray to see how it should return. If it sees that you are asking for a single scalar and not an array, it will just return the first column, of the first row, and set $count to this value.

      I think that DBI::selectcol_arrayref is better when you are returning one and possibly more rows, where each row should contain a single column.

[reply]
[d/l]
[select]
      Dear TadMan,

      You are right! I moved the connect and disconnect out of the function and performance flies now. It was taking about 5 minutes to process 116 records before and now it's around ten seconds.

      I found that it didn't like me using prepare_cached with the same database handle (it complained that "Data type for parameter 1 has changed since first SQLExecute call", so I'm using prepare instead. Still, performance is blazing now.

      I'll see if I can create the library you speak of. Thanks a lot. -timallen

[reply]
[d/l]
        You might be interested in noting that by removing the DBI connect call, if your function has no need to process returned rows (such as via a 'select' statement), your function is then somewhat equivalent to the 'do' method in DBI.
[reply]

Back to Seekers of Perl Wisdom