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]

Hi, I think that your idea is good, different, can you walk me trough the code, just explaining what you had done. Thank you!

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]

• 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.

That said, here is one way -

while (@array) 
{
    my $info_element = shift @array;

    $sql= "SELECT C58XX.CCLMyy, C58XX.CLINyy FROM C58XX
           WHERE C58XX.CCLMyy=$info_element AND 
           C58XX.CLINyy=$X_LINE ";
    ....
}
[download]

my $sql = "SELECT C58XX.CCLMyy, C58XX.CLINyy FROM C58XX
           WHERE C58XX.CCLMyy=? AND C58XX.CLINyy=$X_LINE";
my $sth = $dbh->prepare($sql);

while (@array) {
    $sth->execute($_);    
    ....
}
[download]

If you're sure it's the foreach loop that's causing the slowdown, you could do something simple like:

my $n = 0;
while ( $n < scalar @array ) {
    my $sql = "SELECT blah FROM blah WHERE blah = $array[$n++]
}
[download]

I'd be a little suprised if it was the foreach that was slowing you down, unless you have a monster @array. I'd suggest using Benchmark to compare the while and foreach loops.

--Kester

Whether you use a 'for' or a 'while' loop, loop overhead takes microseconds per iteration. Yes, 'while' loops can be faster when reading in files, because they read in one line at a time, rather than reading in the whole file before processing the lines.

But that doesn't apply here, you start off with an array already in memory.

Consider that an sql query takes milli-seconds, if not seconds or minutes --- far longer than loop overhead.

Since you're looking at improving performance, the first step is to profile .... type perldoc Devel::DProf into a terminal window or search CPAN.org.