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]

In reply to Re^3: Changing foreach loop for a while loop by jZed
in thread Changing foreach loop for a while loop by Anonymous Monk

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.