Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

hi

I am new to perl and would appreciate some pointers on how to improve my db access code. I have some code below to get all rows from a table, select their value for a particular column and then delete any other rows that have that value. I know i could use a more efficient sql query but the more effient query was really slow and this is faster. I was wondering how i should improve my db access code: 

   my $sql_select = "select * from $tableName";

     my $sth = $db->do($str_select);

        $sql_delete = "delete from $tableName where LiftChr=? and Lift
+Pos=?";

     my $sth1 = $db->prepare($sql_delete);

      while ($snp = $sth->fetchrow_hashref) {

          $sth1->execute($sql_delete, undef, $snp->{'LiftChr'}, $snp->
+{'LiftPos'});
    }

[download]
I was also wondering if there were any problems with getting more than one statement handler off the same database connection?

How would i add an 'or die' clause to the select statement when i want to get a statement handler off it? thanks in advance

Replies are listed 'Best First'.
Re: cleaning up dbi code
by Corion (Patriarch) on Nov 26, 2010 at 17:15 UTC

    Are you aware that SQL databases usually have no fixed row ordering? By what criterion do you want to keep/delete a row?

    Also, why don't you roll the deletion into one SQL statement instead?

[reply]
      i did use one delete statement and it took ages so i had a go at decomposing it into lots of delete statements.

      Basically i want to delete any rows that have a the same value for a particular column (in essence this column has to be unique but cant be a key as it is a calculated value and doesnt start off unique)

      I had this code but it takes forever 
      delete b.*
from tbl a
inner join tbl b
on a.col1 = b.col1

      [download]
      so i thought i'll get each row and get its value for the particular column and delete all rows in the db with that value. It's not elegant but will get teh job done.

      But i'm not a perl person and the code i wrote for this just looked awful, plus i've done it wrong too :)
[reply]
[d/l]

        You don't need a full inner join. Maybe try:

        delete
    from a
where a.col1 in (select distinct col1 from b)

        [download]

        But what I wonder about in your SQL is why you have two references to the same table tbl. Do you really want to delete the whole table?

[reply]
[d/l]
[select]
Re: cleaning up dbi code
by mje (Curate) on Nov 26, 2010 at 17:26 UTC

    Your sample is not working code. You are passing your $sql_delete into prepare and then again into execute. The execute call only needs to pass the $snp hashref keys. Don't use the do method for selects - see DBI docs for why. Some DBDs don't support multiple active statements i.e., you cannot issue a new select until you have read all the rows from a previous select.

[reply]
      does mysql support multiple active statements in perl?
[reply]
        i tidied up my code but my main concern was the mlutiple active statement issues - didn't know the right way to do it. thanks 
        my $sql_select = "select * from $tableName";


     $sth->execute or die $sth->errstr;


     
        $sql_delete = "delete from $tableName where LiftChr=? and Lift
+Pos=?";


     my $sth1 = $db->prepare($sql_delete);

     



     while ($snp = $sth->fetchrow_hashref) {

    
          $sth1->execute($snp->{'LiftChr'}, $snp->{'LiftPos'});
    }

        [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom