Hello,
I have to do some database work. I am using 2 loops. One takes a list of data and loops through it. The second loop compares data from the first with its own data. Any way to make this faster instead of opening and closing my dbi? The portion between the **** (of course the *'s are not in the code) is where it slows down. For small amounts of data it works find. For large - well - not so good news.
Any optimizing tips would be welcomed!
### Perform the connection using the Informix driver
+
my $dbh = DBI->connect( "dbi:Informix:nps\@sumitt_aaa","netsite","Ppol
+0699"
,\%attr )or die "Can't connect to database: ", $DBI::errstr, "\n";
+
+
$sth=$dbh->prepare("select det_number,det_ccode,head_date from podetai
+l a,pohead
er b where det_number=head_number and head_date>'2002/01/01'");
+
+
$sth->execute();
+
# will dump out all fields $data=$sth->dump_results();
+
#$data=$sth->dump_results();
+
+
while ( ($po, $ccode,$head_date) = $sth->fetchrow_arr
+ay() ){
#print " $po $ccode, $head_date \n";
+
push (@po,$po);
+
+
}
+
$sth->finish;
+
+
$p_str=@po;
+
print "# of POs: $p_str \n";
+
*********TAKES WAY TOO LONG IN HERE ******************
foreach $po(@po) {
+
for ($count=0; $count<=$i;$count++) {
+
$sth=$dbh->prepare("select det_number from podetail a, poheade
+r b where
det_ccode=? and head_date>'2002/01/01' and det_number=?");
+
#print "old cc: $old[$count], PO checked: $po \n";
+
$sth->execute($old[$count],$po);
+
$p_str=$sth->fetchrow_array();
+
if ($p_str) {
+
push (@p_str,$p_str);
+
print "$p_str \n";
+
}
+
$sth->finish;
+
}
+
} **********
+*********************
Thanks,
V
He who laughs last, doesn't get the joke.
Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
Read Where should I post X? if you're not absolutely sure you're posting in the right place.
Please read these before you post! —
Posts may use any of the Perl Monks Approved HTML tags:
- a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
| |
For: |
|
Use: |
| & | | & |
| < | | < |
| > | | > |
| [ | | [ |
| ] | | ] |
Link using PerlMonks shortcuts! What shortcuts can I use for linking?
See Writeup Formatting Tips and other pages linked from there for more info.