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);
}

[download]

In reply to Re^4: cleaning up dbi code by ruzam
in thread cleaning up dbi code 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.