in reply to DBI performance problem

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.

Replies are listed 'Best First'.
Re: Re: DBI performance problem
by dkubb (Deacon) on Jan 25, 2001 at 12:46 UTC
    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]
Re: Re: DBI performance problem
by zeno (Friar) on Jan 24, 2001 at 19:13 UTC
    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]
        Dear Fastolfe,
        You're right, too. I'll see if I can use do instead on my inserts and updates. Thanks, I really appreciate all of the input I've gotten. -timallen
[reply]

In Section Seekers of Perl Wisdom