时间戳

首页 > 解决方案 > Perl 性能缓慢，文件 I/O 问题或由于 while 循环

database - Perl 性能缓慢，文件 I/O 问题或由于 while 循环

问题描述

我的while循环中有以下代码，而且速度很慢，有什么建议可以改进吗？

open IN, "<$FileDir/$file" || Err( "Failed to open $file at location: $FileDir" );
my $linenum = 0;

while ( $line = <IN> ) {
    if ( $linenum == 0 ) {
        Log(" This is header line : $line");
        $linenum++;
    } else {
        $linenum++;
        my $csv    = Text::CSV_XS->new();
        my $status = $csv->parse($line);
        my @val    = $csv->fields();

        $index = 0;
        Log("number of parameters for this file is: $sth->{NUM_OF_PARAMS}");
        for ( $index = 0; $index <= $#val; $index++ ) {
            if ( $index < $sth->{NUM_OF_PARAMS} ) {
                $sth->bind_param( $index + 1, $val[$index] );
            }
        }

        if ( $sth->execute() ) {
            $ifa_dbh->commit();
        } else {
            Log("line $linenum insert failed");
            $ifa_dbh->rollback();
            exit(1);
        }
    }
}

标签： databaseperformanceperlwhile-loopfile-read

解决方案

到目前为止，最昂贵的操作是访问数据库服务器；这是一次网络旅行，每次数百毫秒或类似的时间。

这些数据库操作是否像它们出现的那样插入？如果是这样，则不是逐行插入，而是为具有多行的语句构造一个字符串insert，原则上与该循环中的行数一样多。然后运行那个事务。

如果行数过多，请根据需要进行测试和缩减。可以继续向插入语句的字符串添加行，直到确定的最大数量，插入该行，然后继续。†</sup>

一些更容易看到的低效率

  • 不要每次都通过循环构造一个对象。在循环之前构建一次，然后在循环中根据需要使用/重新填充。那么这里就不需要parse+fields了，whilegetline也快一点

  • 每次阅读都不需要该if声明。首先读取一行数据，这就是你的标题。然后进入循环，不用ifs

总而言之，没有现在可能不需要的占位符，例如

my $csv = Text::CSV_XS->new({ binary => 1, auto_diag => 1 });

# There's a $table earlier, with its @fields to populate
my $qry = "INSERT into $table (", join(',', @fields), ") VALUES ";

open my $IN, '<', "$FileDir/$file" 
    or Err( "Failed to open $file at location: $FileDir" );

my $header_arrayref = $csv->getline($IN);
Log( "This is header line : @$header_arrayref" );

my @sql_values;
while ( my $row = $csv->getline($IN) ) {       
    # Use as many elements in the row (@$row) as there are @fields
    push @sql_values, '(' . 
        join(',', map { $dbh->quote($_) } @$row[0..$#fields]) . ')';

    # May want to do more to sanitize input further
}

$qry .= join ', ', @sql_values;

# Now $qry is readye. It is
# INSERT into table_name (f1,f2,...) VALUES (v11,v12...), (v21,v22...),...
$dbh->do($qry) or die $DBI::errstr;

我还更正了打开文件时的错误处理，因为||在这种情况下问题中的绑定太紧了，并且有效的open IN, ( "<$FileDir/$file" || Err(...) ). 我们需要or而不是||那里。然后，三个参数open更好。见perlopentut

如果您确实需要占位符，可能是因为您不能有单个插入但必须将其分成多个或出于安全原因，那么您需要为?要插入的每一行生成确切的 -tuples，然后提供正确的它们的值的数量。

可以先组装数据，然后?基于它构建-tuples

my $qry = "INSERT into $table (", join(',', @fields), ") VALUES ";

...

my @data;
while ( my $row = $csv->getline($IN) ) {    
    push @data, [ @$row[0..$#fields] ];
}

# Append the right number of (?,?...),... with the right number of ? in each
$qry .=  join ', ', map { '(' . join(',', ('?')x@$_) . ')' } @data;

# Now $qry is ready to bind and execute
# INSERT into table_name (f1,f2,...) VALUES (?,?,...), (?,?,...), ...
$dbh->do($qry, undef, map { @$_ } @data) or die $DBI::errstr;

这可能会生成一个非常大的字符串，这可能会推动您的 RDBMS 或其他一些资源的限制。在这种情况下，@data分成更小的批次。然后prepare是具有正确数量的(?,?,...)批处理的行值的语句，并execute在批处理的循环中。‡</sup>

最后，另一种方法是使用数据库工具直接从文件中加载数据以实现特定目的。这将比通过要快得多DBI，甚至可能需要将您的输入 CSV 处理成另一个只有所需数据的 CSV。

由于您不需要输入 CSV 文件中的所有数据，因此首先按上述方式读取和处理文件，然后写出仅包含所需数据的文件（@data如上）。那么，有两种可能的方式

  • 要么为此使用 SQL 命令——COPY在 PostgreSQL、LOAD DATA [LOCAL] INFILEMySQL 和 Oracle（等）中；或者，

  • 使用专用工具从您的 RDBMS 导入/加载文件 – mysqlimport(MySQL)、SQL*Loader/ sqlldr(Oracle) 等。我希望这是最快的方法

system这些选项中的第二个也可以在程序之外完成，方法是通过（或者更好地通过合适的库）将适当的工具作为外部命令运行。

†</sup> 在一个应用程序中，我一开始就将多达数百万行放在一起insert——该语句的字符串本身高达几十 MB——并且在单个插入约 100k 行的情况下继续运行每天声明，到现在为止。这是postgresql在好的服务器上，当然还有 ymmv。

‡</sup> 一些 RDBMS 不支持像这里使用的那样的多行（批量）插入查询；特别是甲骨文似乎没有。（最后我们被告知这是这里使用的数据库。）但是在Oracle中还有其他方法可以做到这一点，请查看评论中的链接，并搜索更多。然后脚本将需要构造一个不同的查询，但操作原理是相同的。

推荐阅读