Perl DBI

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

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]

Comment on Perl DBI Download Code

Replies are listed 'Best First'.
Re: Perl DBI by poj (Abbot) on Jun 29, 2013 at 20:25 UTC
my database which has only around 150 records If that is total for all tables then consider extracting all the stat1-4 data in 4 queries first to avoid repeat queries for each record in KPI Master. Also consider replacing * in sql with specific fields that you require. This is untested as I don't have a suitable access database but something like this should run in seconds I would hope ! #!perl use DBI; use strict; my $dbh = DBI->connect('dbi:ODBC:driver=Microsoft Access Driver (.mdb +, .accdb); dbq=D:\Project\Project\BSC_KPIMonitoring\CHN\E_BSC_KPIMonitorDB.mdb'); my @tables = ( 'SDCCHBLOCKING', 'SDCCHDROP', 'TCHBLOCKING', 'TCHDROP', ); my %abbr = ( 'SDCCHBLOCKING' => 'SB', 'SDCCHDROP' => 'SD', 'TCHBLOCKING' => 'TB', 'TCHDROP' => 'TD', ); my $regex = join '\|',@tables; # get all data my %db=(); for my $tbl (@tables){ $db{$tbl} = $dbh->selectall_arrayref("SELECT NE,* FROM $tbl"); } my $sql = "SELECT * FROM KPI_Master"; my $sth = $dbh->prepare($sql); $sth->execute; while ( my ($bsc,$kpi,$thresh) = $sth->fetchrow_array() ){ # search table corresponding to kpi if ($kpi =~ /($regex)/){ my $tbl = $1; for ( @{$db{$tbl}} ){ # select records with correct NE value in first column if (( $_->[0] eq $bsc ) && ( $_->[3] > $thresh )){ print "$abbr{$tbl},$bsc,$thresh,/".$_->[3]."\n"; } } } } [download] poj	[reply] [d/l]
Re^2: Perl DBI by Arunkumarpro (Initiate) on Jun 29, 2013 at 20:49 UTC
Thanks for your response. selectall_arrayref will take more time to select all the records from the table.The performance is more or less similar to mine. It takes around 2-3 minutes of span.Some seconds prior to my code.Also I did not get the required output. Any idea? Anyways I will also work on your code.	[reply]
Re^3: Perl DBI by poj (Abbot) on Jun 30, 2013 at 07:12 UTC
Add table name to * in SQL here ; `$db{$tbl} = $dbh->selectall_arrayref("SELECT NE,$tbl.* FROM $tbl");` [download] Seems that without table name Access interprets * as all fields not already specified. poj	[reply] [d/l]
Re^4: Perl DBI by Arunkumarpro (Initiate) on Jun 30, 2013 at 20:02 UTC
Re^5: Perl DBI by poj (Abbot) on Jun 30, 2013 at 20:09 UTC
Re^4: Perl DBI by Arunkumarpro (Initiate) on Jun 30, 2013 at 20:23 UTC
Re^5: Perl DBI by poj (Abbot) on Jun 30, 2013 at 20:33 UTC
Some notes below your chosen depth have not been shown here
Re: Perl DBI by erix (Prior) on Jun 30, 2013 at 10:35 UTC
If a retrieval of 150 rows takes minutes, I would say something is very wrong. I would separately time different parts (connect, prepare, execute, fetch) with Time::HiRes. You then post those and perhaps something becomes clear.	[reply]
Re^2: Perl DBI by Anonymous Monk on Jun 30, 2013 at 13:15 UTC
There usually is something very wrong when the database in question is Access...	[reply]
Re: Perl DBI by Anonymous Monk on Jun 30, 2013 at 10:03 UTC
It looks like you're dealing with polymorphic relations in SQL. Right now I don't have a set of tables to test with, but you should be able to do the join on the server-side like this (untested): `SELECT m., coalesce(sb.somecol, sd.somecol, tb.somecol, td.somecol) AS someco +l FROM KPI_Master m LEFT JOIN SDCCHBLOCKING sb ON m.NE = sb.NE AND m.KPI = 'SDCCHBLOCKING' LEFT JOIN SDCCHDROP sd ON m.NE = sd.NE AND m.KPI = 'SDCCHDROP' LEFT JOIN TCHBLOCKING tb ON m.NE = tb.NE AND m.KPI = 'TCHBLOCKING' LEFT JOIN TCHDROP td ON m.NE = td.NE AND m.KPI = 'TCHDROP'` [download] This way you should only need to fetch n rows from the database -- where n is the amount of rows you have in KPI_Master -- with only a single round-trip. This should also simplify your code to something like this: my $dbh = DBI->connect('dbi:ODBC:driver=Microsoft Access Driver (.mdb +, .accdb);dbq=D:\Project\Project\BSC_KPIMonitoring\CHN\E_BSC_KPIMoni +torDB.mdb', undef, undef, { RaiseError => 1}); my $query = <<EOF; SELECT m.BSC, m.KPI, m.THRESH, coalesce(sb.somecol, sd.somecol, tb.somecol, td.somecol) AS someco +l FROM KPI_Master m LEFT JOIN SDCCHBLOCKING sb ON m.NE = sb.NE AND m.KPI = 'SDCCHBLOCKING' LEFT JOIN SDCCHDROP sd ON m.NE = sd.NE AND m.KPI = 'SDCCHDROP' LEFT JOIN TCHBLOCKING tb ON m.NE = tb.NE AND m.KPI = 'TCHBLOCKING' LEFT JOIN TCHDROP td ON m.NE = td.NE AND m.KPI = 'TCHDROP' EOF my %kpis = (SDCCHBLOCKING => 'SB', SDCCHDROP => 'SD', TCHBLOCKING => 'TD', TCHDROP => 'TD'); my $sth = $dbh->prepare($query); $sth->execute; while (my @row = $sth->fetchrow_array()) { my ($bsc, $kpi, $thresh, $somecol) = @$row; if ($somecol > $thresh) { print join(",", $kpis{$kpi}, $bsc, $thresh, "/$somecol"); } } [download] Other points of interest for performance might be reducing the amount of columns you get: don't `SELECT `, but `SELECT theOneColumnYouNeed`	[reply] [d/l] [select]
Re: Perl DBI by rpnoble419 (Pilgrim) on Jun 30, 2013 at 03:06 UTC
Have you tried to create an index for NE?	[reply]