时间戳

首页 > 解决方案 > 需要加速 PERL 脚本（每行输入需要 1 秒）

mysql - 需要加速 PERL 脚本（每行输入需要 1 秒）

问题描述

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

样本数据（行）

/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_NUMBER\n";
}

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_id\n";
                }
                if ( ($linecounter > 0) && (($linecounter % 10) == 0) ) {
                    print "We're processing LINE $linecounter\n";
                }
            }
        }
}

close(INFILE);
$stmt->finish();
$dbh->disconnect;

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

标签： mysqlperl

解决方案

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

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

函数sleep将休眠（等待、暂停）作为参数给出的秒数。在你的情况下，2秒。这适用于所有短于 5 个字符的行，给定 if 子句。

删除sleep并查看是否可以解决您的问题。

推荐阅读