Hello all,
I'm just new to DBI, it's my first working DBI script so please don't laugh too hard
at me. The script below works fine (AFAIK) but is slow - inserting 100000 records into
the table takes ~1 hour 20 minutes. I think that's because I placed $sth =
$dbh->prepare in the while loop inside the for block. I tried to place it outside the
for block but some data was missing and some wasn't correct. How can I improve my
script? How can I speed it up? Any advice, any comment and any information are greatly
appreciated.
TIA
Greg
Here goes my script:
#########################
#!perl
use strict;
#use Date::Format;
use DBI;
use diagnostics;
my $dir = "G:\\apps\\quickedi\\ib";
my $recv_file = "padrecv.1";
my $elite_file = "item_upc.txt";
print "Poczekaj chwile..............\n";
open(ITEMUPC, "> $dir/$elite_file ") || die "can't create $dir/$elite_file:
$!";
my %upc_itemlist;
my %item_upclist;
my ($upc_num,$item_num);
my $DSN = "Elite";
my $dbh1 = DBI->connect ("dbi:ODBC:$DSN", "rpt_user", "",
{RaiseError => 1, AutoCommit => 0});
my $SQL = $dbh1->prepare("select upc, item_num from item where org_code = '01' and
item_num = report_item_num and memo_1 <> 'TITLE' and upc is not null and upc <> 'NONE'
order by upc");
$SQL->execute;
while ( my @row = $SQL->fetchrow_array) {
my($upc_num,$item,$price) = @row;
($upc_num = $upc_num) =~
s/^(\d)\s(\d{5})\s(\d{5})\s(\d)|^(\d)(\d{5})(\d{5})(\d)\s{3}/$1$2$3$4/;
($item =$item) =~ s/\s+$//;
print ITEMUPC "$upc_num,$item\n";
}
$dbh1->disconnect;
close ITEMUPC;
open(CHECK, "< $dir/$elite_file");
while (<CHECK>) {
chomp;
($upc_num, $item_num) = split /\s*,\s*/;
$upc_itemlist{$upc_num} = $item_num;
}
close CHECK;
my $dbh = DBI->connect( "dbi:ODBC:EDI Data", "", "" )or die "Can't connect to ODBC
database: $DBI::errstr\n";
open(RECV, "< $dir/$recv_file") || die "can't open $recv_file: $_!";
my @names =
qq(customer,upc_num,item_num,store_num,activity,quantity,beginning_date,ending_date);
my ($count,$customer,$pad_num,$upc,$xq02,$xq03,$za01);
while (<RECV>) {
no warnings 'uninitialized';
s/QA/Current Inventory/;
s/QS/Net Quantity Sold/;
s/.$//;
chomp;
my @fields = split /\</;
if (/^ISA<00< <00< <12<2017781300/) {
$customer = "LNT";
} elsif (/^GS<PD<2017781300/) {
#ignore
} elsif (/^ST<852/) {
$pad_num = $fields[2];
} elsif (/^XQ/) {
my @fields = split /\</;
($xq02 = $fields[2]) =~ s/(\d{4})(\d{2})(\d{2})/$2\/$3\/$1/; #Begining
Date Range
($xq03 = $fields[3]) =~ s/(\d{4})(\d{2})(\d{2})/$2\/$3\/$1/; #Ending
Date Range
} elsif (/^N9/) {
#ignore
} elsif (/^LIN/) {
$upc = $fields[3]; #UPC number
} elsif (/^ZA/) {
$za01 = $fields[1]; #QA = Current Inventory Qty, QS = Net Qty Sold
} elsif (/^SDQ/) {
$upc_itemlist{$upc} = "NO_ELITE_ITEM_NUM_FOR_LNT_UPC" if
length($upc_itemlist{$upc}) == 0;
my @sdq = split/\</;
for (my $i=3;$i<=21;$i+=2) {
if (defined $pad_num) { # because other transactions also have
a SDQ lines
if (defined $sdq[$i]) { # some SDQ lines have less then 22
fields
$count++;
my $sth = $dbh->prepare("INSERT INTO edi_pad (@names) VALUES
('$customer','$upc','$upc_itemlist{$upc}','$sdq[$i]','$za01','$sdq[$i+1]','$xq02','$xq03')");
print "Processing
$count:\t$pad_num,$upc,$sdq[$i],$sdq[$i+1]\n";
$sth->execute or die "can't execute SQL statement:
$DBI::errstr\n";
} else {
#ignore
}
} else {
#ignore
}
}
} elsif (/^CTT/) {
#ignore
} elsif (/^SE/) {
#ignore
} elsif (/^GE/) {
#ignore
} elsif (/^IEA/) {
next unless (/^GS<PD<2017781300/);
} else {
print "Unrecognized: ", substr ($_, 0, 10), "\n";
}
}
close RECV;
$dbh->disconnect;
#######################
and the part of the input file:
ISA<00< <00< <12<2017781300 <12<7084061572
<010611<0537<U<00401<000000588<0<P<:
GS<PD<2017781300<7084061572<20010611<0537<588<X<004010VICS
ST<852<5880001
XQ<H<20010603<20010609
N9<IA<1982
LIN<<UP<009281020035
ZA<QA
SDQ<EA<92<129<5<183<25<187<9<191<9<194<7<206<6<288<16<302<9<305<4<320<30
SDQ<EA<92<329<5<338<6<342<6<346<7<347<8<348<7<350<5<351<5<354<17<355<6
SDQ<EA<92<358<8<359<7<360<8<362<14<363<8<364<7<365<12<367<11<368<3<369<5
SDQ<EA<92<370<7<371<6<372<6<373<11<374<9<375<17<376<10<377<7<378<-2<379<6
ZA<QS
SDQ<EA<92<129<2<187<1<206<1<371<1<387<1<391<1<398<1<400<1<403<1<425<1
SDQ<EA<92<429<1<440<2<441<2<442<1<444<1<446<1<454<1<463<2<470<1<476<1
SDQ<EA<92<478<2<479<1<480<1<483<1<487<1<494<1<498<1<505<1<512<2<514<1
SDQ<EA<92<525<1<528<1<534<1<541<2<549<1<552<1<554<1<562<1<575<1<585<1
SDQ<EA<92<593<1<598<1<616<1<620<2<625<1<627<1<633<2
LIN<<UP<009281021315
ZA<QA
SDQ<EA<92<305<31<320<-3<338<2<346<31<348<95<354<31<355<-2<360<37<373<102<374<9
SDQ<EA<92<377<77<379<-6<381<49<382<2<386<54<390<89<392<84<399<7<402<-5<405<6
SDQ<EA<92<409<74<421<90<425<-1<431<2<437<-3<441<59<462<210<468<18<480<9<494<1
SDQ<EA<92<495<17<502<-1<515<82<523<1<526<-1<527<101<536<9<552<92<555<2<564<-1
SDQ<EA<92<574<58<581<109<586<12<601<16<609<2<616<30<632<1<646<5<654<-2<673<4
ZA<QS
SDQ<EA<92<346<1<360<1<381<1<399<1<436<1<437<1<495<1<515<2<564<1<574<1
CTT<698
SE<8943<5880001
GE<1<588
IEA<1<000000588
