需要加速PERL脚本(每一行输入需要1秒)

Question

下面的Perl脚本每插入一行需要1秒，当有很多行要插入时，这使得它非常低效。如果输入文件的行数超过1,000,000行，则该脚本将花费大约。

示例数据(行)

/mnt/SYN/TEST-Dropbox/Documents/TempBM/10-07-19.docx
/mnt/SYN/CLIENT-DROPBOX-GSUITE/Dropbox-TEST/My Mac (TEST-iMac-Pro.local)/Documents/TempBM/10-07-19.docx
/mnt/SYN/TEST-Dropbox/Documents/TO BE TRANSCRIBED LATER/LASTNAME, FIRSTNAME, Tape 2 (Inaudible)/LASTNAME, FIRSTNAME, TAPE 2 SIDE B (Inaudible).mp3
/mnt/SYN/CLIENT-SYNOLOGY/A. TO BE TRANSCRIBED/*TO BE TRANSCRIBED LATER/LASTNAME, FIRSTNAME, Tape 2 (Inaudible)/LASTNAME, FIRSTNAME, TAPE 2 SIDE B (Inaudible).mp3
/mnt/SYN/CLIENT-SYNOLOGY/DropboxBackup/*TO BE TRANSCRIBED LATER/LASTNAME, FIRSTNAME, Tape 2 (Inaudible)/LASTNAME, FIRSTNAME, TAPE 2 SIDE B (Inaudible).mp3
/mnt/SYN/CLIENT-DROPBOX-GSUITE/Dropbox-TEST/My Mac (TEST-iMac-Pro.local)/Documents/TO BE TRANSCRIBED LATER/LASTNAME, FIRSTNAME, Tape 2 (Inaudible)/LASTNAME, FIRSTNAME, TAPE 2 SIDE B (Inaudible).mp3

脚本

use strict;
use warnings;
use DateTime;
use DBI;
my $inputfile = shift || "-1";
my $startline = shift || "-1";
my $endline = shift || "-1";
if (($inputfile eq "-1") || ($startline eq "-1") || ($endline eq "-1") ){
print "USAGE: $0 <name_of_file.txt> START_LINE_NUMBER END_LINE_NUMBERn";
}
my $group_id = 1;
my $linecounter = 0;
my $dsn = "DBI:MariaDB:Potato1";
my $username = "root";
my $password = "";
my %attr = ( PrintError=>1, RaiseError=>1);
my $dbh = DBI->connect($dsn, $username, $password, %attr);
print "Connected to MYSQL database";
my $sql = "INSERT INTO GMCfiles_tbl(file_id, file_add_date, group_id, filename, status, status_author, status_date) VALUES (?, ?, ?, ?, ?, ?, ?)";
my $stmt = $dbh->prepare($sql);
open(INFILE, "< $inputfile") or die "Could NOT open $inputfile";
while(<INFILE>) {
my $data = $_;
$linecounter++;
if (length($data) < 5) {
$group_id++;
sleep 2;
} else {
my $file_add_date = DateTime->now;
my $filename = $data;
my $status = "New";
my $status_author="";
my $status_date = DateTime->now;
if ( ($linecounter >= $startline) && ($linecounter <= $endline) ) {
if ($stmt->execute($linecounter, $file_add_date, $group_id, $filename, $status, $status_author, $status_date)){
print "Row inserted successfully LINE $linecounter -- GROUP $group_idn";
}
if ( ($linecounter > 0) && (($linecounter % 10) == 0) ) {
print "We're processing LINE $linecountern";
}
}
}
}
close(INFILE);
$stmt->finish();
$dbh->disconnect;

因为这个脚本将运行数天。有人能指出提高效率的方法吗?

Answer 1

这个问题似乎有一个简单的解决方案。在你的代码中有:

if (length($data) < 5) {
$group_id++;
sleep 2;

函数sleep将休眠(等待，暂停)作为参数给定的秒数。在你的例子中，2秒。这适用于所有小于5个字符的行，给定if-子句。

去掉sleep，看看是否能解决你的问题。