my $e = eval( $p );
There's not a lot you can do about that. Writing your own parser would be complicated, error prone and almost certainly eons slower.
The second largest amount of time is being spent building and executing those two complicated SQL statements: $stmt = qq(INSERT INTO ACCU_USAGE VALUES ($Mobil ...
...
$stmt = qq(INSERT INTO ACCU_USAGE VALUES ($MobileNumber, ...
...
$rv = $dbh->do($stmt) or die $DBI::errstr;
...
Now that you can do something about. By pre-preparing the statement using placeholders: my $sql = $dbh->prepare( q[ INSERT INTO ACCU_USAGE VALUES( ?, ?, ?, ?
+, ?, ?, ?, ? ) ] )
or die $DBI::errstr;
and then binding the vars when executing the statement: $rv = $sql->execute(
$MobileNumber,
$value->{'subscriberGroupName'},
$value->{'absoluteAccumulated'}->{'counters'}-
+>[0]->{'bidirVolume'},
$value->{'absoluteAccumulated'}->{'counters'}-
+>[0]->{'name'},
$value->{'selected'},
$value->{'absoluteAccumulated'}->{'expiryDate'
+}->{'volume'},
$value->{'absoluteAccumulated'}->{'previousExp
+iryDate'}->{'time'},
$value->{'absoluteAccumulated'}->{'previousExp
+iryDate'}->{'volume'}
) or die $DBI::errstr;
Not only will you give the DB engine the best chance of performing the inserts optimally; you can also make your code look a lot cleaner and simpler to maintain. It won't reduce your cpu usage; but it should result in your program finishing more quickly.
I hope you'll agree this is much nicer to look at and understand: #!/usr/bin/perl
use Data::Dumper;
use DBI;
use File::Basename;
#use warnings;
@ARGV = qw[ NUL junk.dat CON CON ]; ## crude hack for local testing.
open( FH, "<", $ARGV[0] );
open( AH, "<", $ARGV[1] );
open( OUT, ">", $ARGV[2] );
open( LOG, ">", $ARGV[3] );
#Conneting to database
my $dbExt = "db";
my $driver = "SQLite";
#my $file = basename($ARGV[1]);
#my @fileName = split(/\./, $file);
#$file = $fileName[0] . ".$dbExt";
my $dbFileName = 'junk.db'; #dirname( $ARGV[1] ) ."/" . $file;
if(-e $dbFileName) {
unlink ($dbFileName);
}
my $database = "$dbFileName";
my $dsn = "DBI:$driver:dbname=$database";
my $userid = "";
my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1, Au
+toCommit => 0 })
or die $DBI::errstr;
my $stmt = <<EOS;
CREATE TABLE ACCU_USAGE (
MOBILE VARCHAR2(50),
PLANNAME VARCHAR2(50),
PLANUSAGE CHAR(50),
COUNTER CHAR(20),
STATUS VARCHAR2(20),
EXPIRY_DATE CHAR(20),
PREEXP_TIME CHAR(20),
PREEXP_VOLUME CHAR(20)
);
EOS
my $rv = $dbh->do($stmt);
if($rv < 0){
unlink($dbFileName) if(-e $dbFileName);
$dbh->disconnect();
print LOG $DBI::errstr;
exit(1);
}
my $sql = $dbh->prepare( q[ INSERT INTO ACCU_USAGE VALUES( ?, ?, ?, ?
+, ?, ?, ?, ? ) ] )
or die $DBI::errstr;
my $SubsSize= -s $ARGV[0];
my $AccuSize= -s $ARGV[1];
my $SubsCount=0;
my $AccuCount=0;
my $finalCount=0;
my $rowCount = 0;
while( <AH> ) {
chomp;
my $line = $_;
$AccuCount++;
my $MobileNumber;
if( $line =~ /subscriberId:(\w+)\(\"(\d+)\"\)/ ) {
$MobileNumber = $2;
}
else {
$MobileNumber = "''";
}
my $plan = $line;
$plan =~ s/\\//g;
my @AccVolume;
if( $plan =~ /usageControlAccum:(\w+)\(\"(.*)\"\)/ ) {
my $p = $2;
$p =~ s/:\{/ => {/g;
$p =~ s/:\[/ => [/g;
$p =~s/\"/\'/g;
$p =~ s/\':/\'=>/g;
$p =~ s/\}n/\}/g;
# print $p,"\n";
my $e = eval( $p );
if ( @$ ) {
push (@AccVolume,"error");
} else {
#print Dumper($e);
foreach my $value ( @{$e->{'reportingGroups'}} ) {
if ( exists ( $value->{'absoluteAccumulated'}->{'count
+ers'} ) ) {
$rv = $sql->execute(
$MobileNumber,
$value->{'subscriberGroupName'},
$value->{'absoluteAccumulated'}->{'counters'}-
+>[0]->{'bidirVolume'},
$value->{'absoluteAccumulated'}->{'counters'}-
+>[0]->{'name'},
$value->{'selected'},
$value->{'absoluteAccumulated'}->{'expiryDate'
+}->{'volume'},
$value->{'absoluteAccumulated'}->{'previousExp
+iryDate'}->{'time'},
$value->{'absoluteAccumulated'}->{'previousExp
+iryDate'}->{'volume'}
) or die $DBI::errstr;
}
elsif ( exists ( $value->{'absoluteAccumulated'}->{'bi
+dirVolume'} ) ) {
$rv = $sql->execute(
$MobileNumber,
$value->{'subscriberGroupName'},
$value->{'absoluteAccumulated'}->{'bidirVolume
+'},
$value->{'absoluteAccumulated'}->{'name'},
$value->{'selected'},
$value->{'absoluteAccumulated'}->{'expiryDate'
+}->{'volume'},
$value->{'absoluteAccumulated'}->{'previousExp
+iryDate'}->{'time'},
$value->{'absoluteAccumulated'}->{'previousExp
+iryDate'}->{'volume'}
) or die $DBI::errstr;
}
if($rv < 0) {
print LOG "Failed to insert $stmt query. Exiting..
+.\n";
unlink($dbFileName) if(-e $dbFileName);
print LOG $DBI::errstr;
$dbh->disconnect();
exit(1);
}
else {
$rowCount++;
if($rowCount == 5000) {
$dbh->commit();
$rowCount = 0;
}
}
}
}
}
}
close(AH);
$dbh->commit();
With the rise and rise of 'Social' network sites: 'Computers are making people easier to use everyday'
Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error.
"Science is about questioning the status quo. Questioning authority".
In the absence of evidence, opinion is indistinguishable from prejudice.
|