It wasn't clear to me from your description if your problem is that each SQL statement is taking a long time or if you had a lot of distinct SQL statements you needed to write (i.e., the first two being examples of some long list).

If the problem is writing multiple variations of the same statement a lot of times, you could generate the SQL text more dynamically. For example;

for my $n ( 1..200 ) {
  my $sql = qq{ INSERT INTO moo
                SELECT id$n, escore, org_id$n
                FROM blast, protein
                WHERE id$n = id
                AND text_id = ?};
  [...]
}
[download]

Note the replacement of q{} with qq{} to do interpolation of $n.

It is possible that you could do something on the database side to improve the query preformance once you have identified particular statements that are taking a long time.

If you're not sure what is taking a long time, you might check out DBI::Profile to help identify SQL statements with poor performance.

-- Eric Hammond