Hello, I need help with the code below. It all works and the data is
streamed into the database, but it takes a long time. I'm dealing with
60,000+ records that are being pulled from a continuous growing text
file (that stops after the hour of data is collected). I just want to
see if anyone has suggestions on improving my code. cdlPg.pm
package cdlPg;
use lib '/export/home/motodev/bin/';
use DBI;
#use DBD::mysql;
use DBD::Pg;
use Benchmark;
use IO::File;
use CDMAConfig;
sub new {
my $class = $_[0];
my $objref = {
_mkt => $_[1],
_srvr => $_[2],
_db => $_[3],
_st => $_[4],
_et => $_[5],
_ed => $_[6],
_file => $_[7],
_tbl => $_[8],
_release=> $_[9],
_fstopn => $_[10],
};
bless $objref, $class;
return $objref;
}#end new
sub print_me {
my ($self) = @_;
print "mkt: $self->{_mkt}\n";
print "srvr: $self->{_srvr}\n";
print "db: $self->{_db}\n";
print "st: $self->{_st}\n";
print "et: $self->{_et}\n";
print "ed: $self->{_ed}\n";
print "file: $self->{_file}\n";
print "table: $self->{_tbl}\n";
print "release: $self->{_release}\n";
print "dbh: $self->{_dbh}\n";
my $dbh = $self->{_dbh};
#my $sth = $self->{_sth};
%ENV = %CDMAConfig::MOTOENV;
#open(FHD, ">$self->{_file}");
open(WRITEME, "| /export/home/motorola/local/postgresql7.2.3/bin/psql
+-d $self->{_db} -U kevin -c 'COPY $self->{_tbl} FROM stdin' ") or die
+ "Couln't fork: $! \n";
#$dbh->trace(2);
#$sql = "INSERT INTO $self->{_tbl} VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,
+?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?
+,?,?,?,?,?,?)";
$sql = "SELECT nextval('" . $self->{_tbl} . "_id_seq')";
$sth = $dbh->prepare($sql);
$loop_time=new Benchmark;
while( @kolines = readany( @{$self->{_fstopn}} ) ) {
$number++
foreach my $theline (@kolines) {
if($ver == 16) {
@line = (split(/\|/, $theline))[0,4,5,6,9,10,11,14,18,
+19,21,22,23,25,26,27,28,29,30,31,32,33,34,35,36,37,254,257,258,259,26
+4,265,266,271,272,273,299,300,301,306,307,308,313,314,315,397,398,399
+,400,403,404,406,407,408,409];
} else {
@line = (split(/\|/, $theline))[0,4,5,6,9,10,11,14,18,19,2
+1,22,23,25,26,27,28,29,30,31,32,33,34,35,36,37,211,213,214,215,218,21
+9,220,223,224,225,243,244,245,248,249,250,253,254,255,317,318,319,320
+,322,323,325,326,327,328];
}
if($line[0] eq "")
{
#print "8 is: $line[8]";
$line[0] = '0001-01-01';
}
if($line[8] eq "")
{
#print "8 is: $line[8]";
$line[8] = '00:00:00';
}
if($line[25] eq "")
{
#print "25 is: $line[25]";
$line[25] = '00:00:00';
}
#print join(", ", @line);
$sth->execute;
@rv = $sth->fetchrow_array;
print WRITEME join("\t", @line,$rv[0]) . "\n";
#$dbh->commit();
#($number % 5) == 0 ? $dbh->commit() : next;
}#end foreach
}#end while
close(WRITEME);
$end_time=new Benchmark;
open(KOOUT, ">>/export/home/motodev/kobench.txt");
print KOOUT "$self->{_tbl} the code took: " . timestr(timediff($end_t
+ime, $loop_time),"all") . "\n\n";
close(KOOUT);
$sth->finish();
$dbh->disconnect();
}
sub readany {
my $cnt = 0;
my @res;
for my $fh ( @_ ) {
next if eof($fh);
my $line = <$fh>;
$cnt++ if defined($line);
push @res, $line;
}
return if ! $cnt;
return @res;
}#end readany
sub getData {
my ($self) = @_;
my @files = @{$self->{_fstopn}};
foreach my $file (@files) {
local *FILE;
open(FILE, "<$file") or die "can't open file $!\n";
push(@filehandles, *FILE);
}
$self->{_fstopn} = \@filehandles;
&print_me;
}
# Establish the connection and create the table for this data - return
+ database connection with prepared statement
sub connectDB {
my ($self) = @_;
$dbh = DBI->connect("DBI:Pg:dbname=$self->{_db};host=se-srvr1;port
+=5432", 'kevin', 'passwordhere', { RaiseError => 1, AutoCommit => 0 }
+);
$dbh->do("CREATE TABLE $self->{_tbl} (
date date DEFAULT '0001-01-01',
cdl_seq_num int4 DEFAULT 0,
call_ref_num int4 DEFAULT 0,
cbsc int4 DEFAULT 0,
cpp int4,
mid char(10),
esn int8 DEFAULT 0,
dialed_digits char(32),
access_time time DEFAULT '00:00:00',
access_pn_offset int4,
access_channel int4,
access_bts int4 DEFAULT '0',
access_sector int4,
service_option int4,
negotiated_so int4,
last_mm_setup_event int4,
cic_span int4,
cic_slot int4,
xcdr int4,
init_rf_conn_bts int4,
init_rf_conn_sector int4,
init_rf_conn_mcc int4,
init_rf_conn_element int4,
init_rf_conn_channel int4,
cfc int4,
release_time time DEFAULT '00:00:00',
last_maho_cand_count int4,
last_maho_act1_bts int4,
last_maho_act1_sector int4,
last_maho_act1_str int2,
last_maho_act2_bts int4,
last_maho_act2_sector int4,
last_maho_act2_str int2,
last_maho_act3_bts int4,
last_maho_act3_sector int4,
last_maho_act3_str int2,
last_maho_cand1_bts int4,
last_maho_cand1_sector int4,
last_maho_cand1_str int2,
last_maho_cand2_bts int4,
last_maho_cand2_sector int4,
last_maho_cand2_str int2,
last_maho_cand3_bts int4,
last_maho_cand3_sector int4,
last_maho_cand3_str int2,
last_sho_bts int4,
last_sho_sector int4,
last_sho_mcc int4,
last_sho_element int4,
fwd_quality int4,
last_fwd_incr int4,
rvs_quality int4,
last_rvs_incr int4,
rvs_erase_count int4,
rf_fade_count int4,
id serial)") || print "Error is: $dbh->errstr\n";
$dbh->commit();
$self->{_dbh} = $dbh;
return $self->{_dbh};
}
1;
Here's the code that calls the module: oocdlbPg.pl
use lib '/export/home/motodev/bin/';
use Getopt::Std;
use Date::Manip;
use CDMAConfig;
use cdlPg;
getopt('ZDHCO');
# Get command line parameters
# -Z - which market to produce reports for - separate each market by a
+ comma - default is all
# -D - Date
# -H - Hour
# -C - Crontab run - if set to 1, means that the script is being calle
+d from the crontab
# and certain variables should be adjusted accordingly.
# -O - OMC to run
#
# -R - Force collection ??? - NOT CURRENLTY SUPPORTED
#
# Variables produced are $opt_T, $opt_Z, $opt_D, $opt_H and $opt_C
if($opt_C == "1") {
#Script being called from cron.....set date and hour to cu
+rrent time.
$hour = (localtime)[2];
$hour = sprintf ("%02d", $hour); #find previous hour
$day = &UnixDate("today","%y%m%d");
$dispday = &UnixDate("today","%Y%m%d");
$date = $day . $hour;
} else {
$opt_H ? ( $hour = $opt_H ) : ( die "Must supply Hour\n" );
$opt_D ? ( $day = $opt_D ) : ( die "Must supply Date\n" );
$date = $day . $hour;
$dispday = "20" . $day . $hour;
}
# Error checking for parameter variables
$opt_Z ? ( $mkt = $opt_Z ) : ( die "Must supply Market\n" );
#$opt_O ? ( $omc = $opt_O ) : ( die "Must supply OMC\n" );
$rel = $CDMAConfig::MAR_CDL_FORMAT{$mkt};
$omcaddr = $CDMAConfig::OMC_ADDRESSES{$mkt}{$omc};
$begin = $hour . "0000";
$end = $hour . "5959";
$file = $CDMAConfig::REPORT_PATH . $mkt . "/pdfs/$dispday$hour.$mkt.$r
+el.$omc.Pg.pdfs";
#$file = "/export/home/motorola/cdl_pdf/" . $mkt . "/$dispday$hour.$mk
+t.r$rel.omc$omc.pdfs";
$table = "cdl_" . $dispday . $hour;
#print "$mkt, $omcaddr, $mkt, $begin, $end, $day, $file, $table, $rel"
+;
%mktomcs = %{$CDMAConfig::OMC_ADDRESSES{$mkt}};
@mktkys = @{[ keys %mktomcs ]};
foreach $mktky (@mktkys) {
my $file = "/export/home/motorola/cdl_pdf/" . $mkt . "/$dispday$ho
+ur.$mkt.r$rel.omc$mktky.pdfs";
push @fstopn, $file;
}
#print join("\n", @fstopn);
#@fstopn = ("/home/kevin/tmp/file1","/home/kevin/tmp/file2");
$kocdl = cdlPg->new($mkt, $omcaddr, $mkt, $begin, $end, $day, $file, $
+table, $rel, \@fstopn);
$dbcon = $kocdl->connectDB;
$kocdl->getData($dbcon);
__END__
I know the code looks rough, but hopefully someone can help. Any help is
greatly appreciated!!!
Kevin
--
Kevin Old <[EMAIL PROTECTED]>
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
