vivekvp has asked for the wisdom of the Perl Monks concerning the following question:

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;                                                         
+          
        }                                                             
+          
}                                                           **********
+*********************

[download]
Thanks, V He who laughs last, doesn't get the joke.

Replies are listed 'Best First'.
Re: VVP:Faster DBI'ing?
by Zaxo (Archbishop) on Mar 12, 2002 at 16:11 UTC

    use strict; use warnings; will catch that $i is undefined as you enter the c-style for loop, It will catch that @old is undefined, too.

    It will not catch that you prepare a DBI statement handle with placeholders each time through the loop. Move it outside to reduce overhead.

    Could you clarifiy what you are trying to do?

    After Compline,
    Zaxo

[reply]
Re: VVP:Faster DBI'ing?
by rdfield (Priest) on Mar 12, 2002 at 16:01 UTC
    You're getting a cartesian product in the second sth definition. You need to have something in your where clause that relates podetail and poheader (like in the first sth definition).

    rdfield

[reply]

      Aside from the cartesian issue that rdfield and derby note, you might also get a speed increase by using prepare_cached or moving the second prepare outside the foreach loop so that you're not (re)prepareing the same statement over and over (especially silly when you go to the trouble of using placeholders!).

      $sth = $dbh->prepare(...);
foreach $po (@po) {
   for ($count=0; $count<=$i;$count++) {
        $sth->execute($old[$count],$po);
        # ...
   }
}

      [download]

          --k.


[reply]
[d/l]
        Kanji,

        Not true in Oracle - the compiled statement would be cached and re-used. I would use your suggestion though - better style.

        rdfield

[reply]
Re: VVP:Faster DBI'ing?
by derby (Abbot) on Mar 12, 2002 at 16:01 UTC
    vivekvp,

    That second sql looks funny: 

    select det_number 
from podetail a, poheader b
where
        det_ccode=?
  and head_date > '2002/01/01'
  and det_number=?

    [download]

    I don't see where the join between podetail and poheader take place. I think you're ending up with a cartesian product. How do podetail and poheader relate (besides, and I assume here, podetail.det_number and poheader.head_number).

    -derby

[reply]
[d/l]
Re: VVP:Faster DBI'ing?
by krazken (Scribe) on Mar 12, 2002 at 16:07 UTC
    I don't think all of your code got in here. Where did the 
    @old

    [download]
    come from? Also, make sure that you have indexes on your join fields. It looks like you are joining on one thing for one query for the first on and something different for the second one. Is this correct? Just from an efficiency standpoint, your code will run slower during that block of code because you are doing 2 exact comparisons and one date range. You might try creating a view before hand that has the date range already in it, then you can just query the view, and you won't have to rummage through the whole table looking for the information. Another way would be to run the first query as is, then run the second one without the ccode and number check, and then check the results once you get it all in memory against what you are looking for. Also, move your $db->prepare outside of your for loop. Since that query is static, there is no need to redeclare it every time. You will eat up the database SGA in a hurry!!!! Look at bind variables with DBI to get around the SGA/PGA stuff. Hope this helps you get started cheers! krazken
[reply]
[d/l]
[select]
Re: VVP:Faster DBI'ing?
by mpeppler (Vicar) on Mar 12, 2002 at 17:30 UTC
    As others have mentioned we seem to be missing some code here.
    However - to give a (potentially) different type of solution - it would seem that you should be able to do this in a single SQL statement. I can't do it now because I don't know what @old is, nor what $i is supposed to be (though I suspect that $i == scalar(@old)).

    Michael

[reply]
Re: VVP:Faster DBI'ing?
by vivekvp (Acolyte) on Mar 12, 2002 at 17:22 UTC
    Hmmm... Sorry for not including the whole code...here it is. I moved the prepare statement outside the loop. The reason the tables are not joined - the fields I am using are uniquely named - so I do not need to reference them (ie a.field1=b.field2) - do I? Here is the whole code (my apologies for my poor style): 
    #!/usr/bin/perl -w                                                    
+      
### you know you can probably do this with temp tables in SQL....VVP  
+      
### Description:  change old ccodes with new ones provided            
+      
### Will affect rtsdetail table - ccode field                         
+      
                                                                      
+      
use DBI;            # Load the DBI module                             
+      
use CGI;        # HTML                                                
+      
use strict;     # keep it usable                                      
+      
use CGI qw(:standard);                                                
+      
use CGI::Carp qw( fatalsToBrowser );                                  
+      
                                                                      
+      
                                                                      
+      
###                                                                   
+      
my ($sth);                                                            
+      
my $ccode;                                                            
+      
my(@data,$data);                                                      
+      
my ($p_str,@p_str);                                                   
+      
my $i=0;  # counter                                                   
+      
my ($CC,@CC);                                                         
+      
my ($po,@po);                                                         
+      
my ($old,$new);                                                       
+      
my (@old,@new);                                                       
+      
my $count;                                                            
+      
my $total;                                                            
+      
my $head_date;                                                        
+      
                                                                      
+      
open (CC,"ccchg.txt");                                                
+      
        while ($_=<CC>) {                                             
+      
        push (@CC,$_);                                                
+      
        ($old,$new)=split(/\|/,$_);                                   
+      
        push @old,$old;                                               
+      
        push @new,$new;                                               
+      
        }                                                             
+      
close (POCOL);                                                        
+      
                                                                      
+      
                                                                      
+      
$i=@new;                                                              
+      
#print "$i - EYE \n";                                                 
+      
                                                                      
+      
### Attributes to pass to DBI->connect() to disable automatic         
+      
### error checking                                                    
+      
my %attr = (                                                          
+      
    PrintError => 0,                                                  
+      
    RaiseError => 0,                                                  
+      
);                                                                    
+      
                                                                      
+      
### 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,po
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";                                          
+      
        $sth=$dbh->prepare("select a.det_number from podetail a, pohea
+der b 
e det_ccode=? and head_date>'2002/01/01' and a.det_number=?");        
+      
foreach $po(@po) {                                                    
+      
        for ($count=0; $count<=$i;$count++) {                         
+      
        #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;                                                         
+      
        }                                                             
+      
}                                                                     
+      
$total=@p_str;                                                        
+      
print "Total: $total  AND @p_str \n";                                 
+      
$total=@po;                                                           
+      
print "POs used: $total \n";

    [download]
    Thanks, V He who laughs last, doesn't get the joke.
[reply]
[d/l]

Back to Seekers of Perl Wisdom