Private
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] = ' ';
}
}