in reply to Perl DBI

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

Replies are listed 'Best First'.
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]

      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]

        Can u please explain this statement?what is the reason for placing the NE as the first column?

        arun

[reply]

        SELECT * FROM $tbl; this statement also has the same effect? what is the differnce between your statement and the statement above?(may be in terms of performance)

        arun

[reply]

In Section Seekers of Perl Wisdom