Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number

Re^4: cleaning up dbi code

by ruzam (Curate)
on Nov 27, 2010 at 04:18 UTC ( #873964=note: print w/replies, xml ) Need Help??

in reply to Re^3: cleaning up dbi code
in thread cleaning up dbi code

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

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://873964]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (6)
As of 2018-04-21 16:33 GMT
Find Nodes?
    Voting Booth?