You're only using the LiftChr and LiftPos columns in your delete, but you're selecting the whole row in your fetch. I don't know what else is in your table. It might just be one more column, or it could be huge, it could even be a view that joins many other tables. Selecting the whole row is just causing your code to shuffle extra data back and forth that won't be used. I would limit your first select to LiftChr and LiftPos for starters instead of *.
You can eliminate multiple active statements and speed things up at the same time by concatenating your two desired keys and then splitting them again. Instead of repeatedly fetching each row and diving in and out of the DBI code, you can do one request to get all the keys and let Perl walk through them. It's far faster for Perl to loop without calling fetches, and it's far faster for DBI to get and return all the keys at once. The only caveat is that Perl has to be able to hold all the keys. Depending on the size of your data that could be a problem.
Adding 'distinct' to the query eliminates duplicate keys at the source so (a) they don't have to be transfered into your code, and (b) you don't have to waste time deleting the same keys over and over.
# choose an appropriate separator char, I like to use ':'
# using concat() gives us a single column result for a two
# (or more) column need
my $sql_select =
"select distinct concat(LiftChr, ':', LiftPos) from $tableName";
# since we're requesting a single final column, we can use
# the selectcol_arrayref() to get all values at once
my $array_ref = $db->selectcol_arrayref($sql_select);
# or die? check for errors!
# now we have all keys and we're not in an active statement either
my $sql_delete =
"delete from $tableName where LiftChr=? and LiftPos=?";
my $sth1 = $db->prepare($sql_delete);
# this loop steps through a simple array
# waaay faster than fetching row by row
foreach (@$array_ref) {
# separate the 'single' column back into it's parts
my ($chr, $pos) = split(/:/, $_);
# profit
$sth1->execute($chr, $pos);
}
