in reply to Changing foreach loop for a while loop

You don't need a loop at all. Just use the SQL, Luke! 
my $p_str = join ',', ('?') x @array;
my $sql = "
    SELECT C58XX.CCLMyy, C58XX.CLINyy
      FROM C58XX
     WHERE C58XX.CCLMyy IN ( $p_str )
       AND C58XX.CLINyy = $X_LINE
";
my $sth = $dbh->prepare( $sql );
$sth->execute( @array );

[download]

Replies are listed 'Best First'.
Re^2: Changing foreach loop for a while loop
by Anonymous Monk on Dec 30, 2004 at 13:09 UTC
    Hi, I think that your idea is good, different, can you walk me trough the code, just explaining what you had done. Thank you!
[reply]
      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]
[reply]
[d/l]
[select]
      • He changed the SQL statement to use IN, which will match against a list of things, instead using equals to match against one thing
      • He used positional parameters in the SQL statement: one question mark for each thing to be filled in, in the IN clause
      • He passed the array to the execute statement, which plugged in one value from the array for each of the question marks in the SQL statement
      If your array is huge, the SQL processor may not take that many values in an IN list, in which case you need to break up your array into acceptable-sized pieces.
      Caution: Contents may have been coded under pressure.
[reply]
        If you array is huge, you could also create a temporary table, put the values into the temporary table, and perform a join.
[reply]

In Section Seekers of Perl Wisdom