Hi greg,
This should speed it up:
Take the "my $sth = $dbh->prepare("INSERT INTO edi_pad (@names) VALUES
('$customer','$upc','$upc_itemlist{$upc}','$sdq[$i]','$za01','$sdq[$i+1]','$
xq02','$xq03')");" line outside the while loop and replace it with :
my $sth = $dbh->prepare("INSERT INTO edi_pad (@names) VALUES
(?,?,?,?,?,?,?,?)");
Inside the while loop put
$sth->execute($customer,$upc,$upc_itemlist{$upc},$sdq[$i],$za01,$sdq[$i+1],$
xq02,$xq03);
That should do it.
HTH
Andy
-----Original Message-----
From: Greg Wardawy [mailto:[EMAIL PROTECTED]]
Sent: Friday, June 15, 2001 5:34 PM
To: [EMAIL PROTECTED]
Subject: How can I speed it up?
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,endi
ng_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
The information contained in this communication is
confidential, is intended only for the use of the recipient
named above, and may be legally privileged. If the reader
of this message is not the intended recipient, you are
hereby notified that any dissemination, distribution or
copying of this communication is strictly prohibited.
If you have received this communication in error, please
re-send this communication to the sender and delete the
original message or any copy of it from your computer
system.
