troubleshooting Question

Help with Perl While Loop

Avatar of justmorri
justmorriFlag for United States of America asked on
Programming Languages-OtherOracle DatabasePerl
37 Comments1 Solution640 ViewsLast Modified:
Is there any way to make this script run faster? Right now it is taking almost an hour to
process a 91,000 flat file. Here is the while loop code which runs through each line in the
file and insert it into an oracle database table.  

my $line = 0;
my $c = 0;

while (<IN>) {
  chomp($_);
  $line += 1;

  my ($field1,
    $field2,
    $field3,
    $field4,
    $field5,
    $field6,
    $field7,
    $field8,
    $field9,
    $field10,
    $field11,
    $field12,
    $field13,
    $field14,
    $field15,
    $field16,
    $field17,
    $field18,
    $field19,
    $field20,
    $field21,
    $field22,
    $field23,
    $field24) = split(/ /,$_);

  &fix($field1);
  &fix($field2);
  &fix($field3);
  &fix($field4);
  &fix($field5);
  &fix($field6);
  &fix($field7);
  &fix($field8);
  &fix($field9);
  &fix($field10);
  &fix($field11);
  &fix($field12);
  &fix($field13);
  &fix($field14);
  &fix($field15);
  &fix($field16);
  &fix($field17);
  &fix($field18);
  &fix($field19);
  &fix($field20);
  &fix($field21);
  &fix($field22);                
  &fix($field23);
  &fix($field24);
     
  if ($field1 ne ' ') {
    $field1 =~ s/\///g;
    my ($month, $day, $year) = unpack "A2A2A4", $field1;  
    $field1 = $year.''.$month.''.$day;                    
  }
 
  $field23 = lc($field23);
  $field24 = lc($field24);

  if (($field15 ne "A") && ($field16 ne "B")) {
    eval {
      $sql = "insert into myTable values (\'$field1\',\'$field2\',\'$field3\',\'$field4\',\'$field5\',\'$field6\',\'$field7\',\
'$field8\',\'$field9\',\'$field10\',\'$field11\',\'$field12\',\'$field13\',\'$field14\',\'$field15\',\'$field16\',\
'$field17\',\'$field18\',\'$field19\',\'$field20\',\'$field21\',\'$field22\',\'$field23\',\'$field24\') ";
      $sth = $dbh->prepare($sql);
      $sth->execute();
    };
    if ($@) { print "$@\n"; next; }
    $dbh->commit();
    $c += 1;
  }
}


Subroutine that's called -

sub fix {
  $_[0] =~ s/^ *//;
  $_[0] =~ s/ *$//;              
  $_[0] =~ s/'/''/g;            
  if (! $_[0]) {
    $_[0] = ' ';
    }
  }
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 37 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 37 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros