I am using to Perl DBI to select and process data from an MS Access database.The code works well but it is very slow.It takes 3-4 minutes to select data from my database which has only around 150 records. I am preparing the statements outside the loop only.Tried using the fetchrow_array,fetchrow_arrayref,fetchall_arrayref methods,but the performance does not differ much.How can i improve the speed of execution?

my $dbh = DBI->connect('dbi:ODBC:driver=Microsoft Access Driver (*.mdb
+, *.accdb);dbq=D:\Project\Project\BSC_KPIMonitoring\CHN\E_BSC_KPIMoni
+torDB.mdb');
my $stat1="SELECT * FROM SDCCHBLOCKING WHERE NE=?";
my $sth1 = $dbh->prepare($stat1);
my $stat2="SELECT * FROM SDCCHDROP WHERE NE=?";
my $sth2 = $dbh->prepare($stat2);
my $stat3="SELECT * FROM TCHBLOCKING WHERE NE=?";
my $sth3 = $dbh->prepare($stat3);
my $stat4="SELECT * FROM TCHDROP WHERE NE=?";
my $sth4 = $dbh->prepare($stat4);

my $sqlstatement="SELECT * FROM KPI_Master";
my $sth = $dbh->prepare($sqlstatement);
$sth->execute ||
  die "Could not execute SQL statement ... maybe invalid?";
while (my @row=$sth->fetchrow_array()){
$bsc = $row[0];
$kpi = $row[1]; 
$thresh= $row[2];

if($kpi eq "SDCCHBLOCKING")
{
$sth1->execute($bsc) ||
 die "Could not execute SQL statement ... maybe invalid?";
while (my @row1=$sth1->fetchrow_array())
  {
           if($row1[2]>$thresh)
          {
              print "SB,$bsc,$thresh,/$row1[2]";
          }
  }
 }

 if($kpi eq "SDCCHDROP")
  {
  $sth2->execute($bsc) ||
 die "Could not execute SQL statement ... maybe invalid?";
while (my @row2=$sth2->fetchrow_array())
 {
    if($row2[2]>$thresh)
          {
              print "SD,$bsc,$thresh,/$row2[2]";
          }
  }
}

if($kpi eq "TCHBLOCKING")
  {
   $sth3->execute($bsc) ||
     die "Could not execute SQL statement ... maybe invalid?";
 while (my @row3=$sth3->fetchrow_array())
 {
    if($row3[2]>$thresh)
          {
              print "TB,$bsc,$thresh,/$row3[2]";
          }
   }
 }
if($kpi eq "TCHDROP")
 {
   $sth4->execute($bsc) ||
     die "Could not execute SQL statement ... maybe invalid?";
  while (my @row4=$sth4->fetchrow_array())
   {
        if($row4[2]>$thresh)
          {
              print "TD,$bsc,$thresh,/$row4[2]";
          }
    }
  }

[download]

In reply to Perl DBI by Arunkumarpro

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • 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:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.