Well, the biggest thing is to realize that the SQL predicate IN allows you to search for multiple values in a single trip to the database. Rather than looping through values in perl and making a trip to the database for each one, you just make one trip and ask about all the values at once. There are some things that can be done quicker in perl and some things that can be done quicker in a database and this is an example of a case where, no matter how much you optimize your perl, your main loss of time is going to be in trips to the database so you want to think about shifting the load away from perl and toward SQL.

The only other tricky thing I have done is to dynamically generate a string of placeholders. If you do a super-search here on the site, you'll see many postings extolling the benefits of placeholders. The basic idea is that you put in question marks when you prepare your SQL to act as placeholders for values and then substitute in the values when you execute the statement.

In this case, since we (or at least I) don't know the number of elements in @array, we need to dynmaically construct a placeholder string containing the same number of question marks as there are values in the array. The perl "x" multiplication allows us to make as many question marks as there are values in the array and the join function allows us to join the question marks with commas:

my $p_str = join ',', ('?') x @array;
[download]

If you print out that placeholder string, you'll see that it contains as many question marks as there are in the array all separated by commas. We then put that into the SQL. If we were doing this with three values "a", "b", "c" instead of the array, the placeholder string would look like "?,?,?" and we'd use it like this:

my $sql = "SELECT foo FROM bar WHERE baz IN (?,?,?)";
my $sth = $dbh->prepare( $sql );
$sth->execute( "a", "b", "c");
[download]