As suggested here you need to Benchmark - there are a range of options as noted.

As far as you code goes you database work is pretty ordinary. Given you are connecting to a DB and then executing sql you should cache as much as possible. Database connections, STH preparation all take time (lots of it) so you want to avoid as much as possible. You use our $dbh but don't use $dbh ||= connect so you are making a connection with every call to your main subroutine. It seems you are also not aware of ? bind placeholders. You also appear not either finishing your STHs or disconnecting from the DB. You should be doing something like this (broad brush):

package Main;

my $dbh = connect.....
my $sth_cache;
$sth_cache->{query1} = $dbh->prepare_cached('SELECT foo FROM bar WHERE
+ foo = ?');
$sth_cache->{query2} = $dbh->prepare_cached('SELECT foo1 FROM bar1 WHE
+RE foo1 = ?');
END{
    $sth_cache->{$_}->finish for keys %{$sth_cache};
    $dbh->disconnect if $dbh;
}

#blah

sub widget {
    $sth_cache->{query1}->execute($bind_value_for_foo_WHERE_clause);
    $res = sth->fetchall_arrayref();
    # blah
}
[download]

This will be faster. You have only given us an incomplete chunk of your code so it is hard to say what else you could do (other than loose a lot of the if/elsif as suggested). Note fetchall_arrayref is often several times faster than fetching in a loop. I would note that you are effectively generating a fetchall_array_ref in a loop which is a waste. As always YMMV and Benchmarking will show you the ONE TRUE WAY. The one true way is not always worth pursuing as THE ALMOST TRUE WAY THAT TOOK ME FAR LESS TIME TO FIND is ofen good enough to get the job done.

As a general DB thing if you have a WHERE foo = ... clause and a big DB and you don't have a primary key, unique index or a least an index on 'foo' then you are doing a linear search through EVERY record in that table. In a big table (say a million records) this will take seconds without and index (roughly 2-3 on the current top of the range Linux type Xeon/RaidX hardware). It will take microseconds with an index. If you are doing that you are wasting the power of the DB and might as well use a flat file....

Speaking of hardware you can almost never have enough memory for a DB or Perl. You can almost always spend memory and gain speed.

cheers

tachyon

s&&rsenoyhcatreve&&&s&n.+t&"$'$`$\"$\&"&ee&&y&srve&&d&&print