in reply to Perl Code Efficiency Issue

One thing that jumps out at me is that you're constantly re-prepare-ing your UPDATE statement inside the last while loop. You should prepare it once outside the loop with placeholders (instead of interpolating into the string; in fact you should do that with your other statements as well), then just call execute with the values you've extracted out.

Another performance hit might be that you're using fetchrow_hashref; I want to say using this rather than (say) fetchrow_arrayref incurs a higher overhead (since it's got to parse and populate a hashref rather than just returning a set of values).

The cake is a lie.
The cake is a lie.
The cake is a lie.

Replies are listed 'Best First'.
Re^2: Perl Code Efficiency Issue
by Anonymous Monk on Feb 15, 2008 at 15:57 UTC
    Can you give an example on how I would do that?
[reply]
      Before the loop: 
      $sql_net="UPDATE today_records
        SET current_prem= ?
        WHERE  account_num= ? AND type='OK'";
my $sth2 = $dbh_2->prepare($sql_net);

      [download]
      In the loop:
              $sth2->execute(CONVERT(money, '$ABC7'),'$ABC2') || die $sth2->
+errstr;

      [download]
      Update: Changed name of $sth, since previous version is still in scope.
[reply]
[d/l]
[select]

        Erm ... CONVERT is a SQL function, not a Perl one, likewise the money is a SQL constant, not a Perl one. And is you enclose a variable in singlequotes you do not get the value of the variable inserted into the string, but the dollar and the name of the variable. I believe you meant: 

        $sql_net="UPDATE today_records
        SET current_prem = CONVERT(money, ?)
        WHERE  account_num= ? AND type='OK'";
my $sth2 = $dbh_2->prepare($sql_net);

...

$sth2->execute( $ABC7, $ABC2) || die $sth2->errstr;

        [download]
        Now, while it might make sense to name the statement handle $sth2, it definitely does not make sense to name variables $ABC1, $ABC2, ... It should be an array!

        BTW, are you sure the CONVERT() is needed?

        Jenda
        Support Denmark!
        Defend the free world!

[reply]
[d/l]
        Is this what you're trying to say?
        #Get all accounts for current term:::

my $sql_back="SELECT ABC1,ABC2,ABC3,ABC4,ABC5,ABC6, ABC7, ABC8
                         FROM TBIBM
                         WHERE ABC2='$account_num'  AND ABC6 = '$accou
+nt_due_date' AND ABC5='$n_year'";

my $sth = $dbh->prepare($sql_back);
   $sth->execute() || die $sth->errstr;

        $sql_net="UPDATE today_records
                  SET current_prem= ?
                  WHERE  account_num= ? AND type='OK'";
        my $sth2 = $dbh_2->prepare($sql_net);
        
    while (my $pointer = $sth->fetchrow_hashref){
          $count++;
       $ABC1 = $pointer->{'ABC1'}; 
       $ABC2 = $pointer->{'ABC2'}; 
           $ABC3 = $pointer->{'ABC3'};
           $ABC4 = $pointer->{'ABC4'};
       $ABC5 = $pointer->{'ABC5'};
       $ABC6 = $pointer->{'ABC6'}; 
       $ABC7 = $pointer->{'ABC7'}; 
           $ABC8 = $pointer->{'ABC8'};

       #done getting all accounts , need to update web db once again w
+ith new data!

                      #$sql_net="UPDATE today_records
                        #SET current_prem= CONVERT(money, '$ABC7')
                        #WHERE  account_num='$ABC2' AND type='OK'";
                        #my $sth = $dbh_2->prepare($sql_net);
                        #$sth->execute() || die $sth->errstr;
                        
                      $sth2->execute(CONVERT(money, '$ABC7'),'$ABC2') 
+|| die $sth2->errstr;    

    }

        [download]
[reply]
[d/l]

      If you want to be truly lazy, simply replace prepare by prepare_cached, and DBI itself will check if it has ever seen the query before, and will return an appropriate context for your statement handler (which in turn should hit a query plan cached by the DB backend).

      • another intruder with the mooring in the heart of the Perl

[reply]

In Section Seekers of Perl Wisdom