AnsweredAssumed Answered

OLEDB query in Perl script runs slow.

Question asked by AlistairFrith on Dec 13, 2019
Latest reply on Dec 16, 2019 by gregor

We have a Perl script including the query listed below, which is used to obtain 1-year average values for around 15,000 tags (each of which has 48 values per day). Sometimes it run fast but other times is slows down around 1/3 of the way through and never speeds up again. It has been implemented as a Perl script because Excel would freeze and timeout. Here's the salient part of the script:

 

while ($row = $csv->getline($tags_file) )

{

   $output_file_lines++;

   my $tag = $row->[0];

   ## append to the output file

   open(my $output_file, '>>', $output_filename) or die "Could not open log file '$output_filename' $!";

   # Create a command object to use for queries

   our $pi_cmd2 = Win32::OLE->new('ADODB.Command');

   die error() if error();

   # we have to associate the command object with a connection in order to be able to use it.

   $pi_cmd2->{ActiveConnection} = $pi_conn2;

   # set the sql query text

   $pi_cmd2->{CommandText} = "SELECT tag, value, pctgood FROM piavg WHERE tag = '$tag' AND (time between '*-365d' AND '*') ";

   die error() if error();

   # execute the command object and get the result set to work with.

   our $pi_rs2 = $pi_cmd2->execute();

   die error() if error();

   # prime to the first row in the result set.

   $pi_rs2->MoveFirst();

   $percent_complete = 100 * $output_file_lines / $tag_file_lines;

   print "\n Progress = $percent_complete%. Tag: $tag Average:" . $pi_rs2->fields('value')->{value} . " Pctgood: " . $pi_rs2->fields('pctgood')->{value} . "\n";

   print $output_file $pi_rs2->fields('tag')->{value} . "," . $pi_rs2->fields('value')->{value} . "," . $pi_rs2->fields('pctgood')->{value} . "\n"; close $output_file;

};

$pi_conn2->Close;

close $tags_file;

 

Any idea why this code doesn't maintain a steady speed?

Outcomes