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.

In reply to Re: DBI performance problem by tadman
in thread DBI performance problem by zeno

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.