使用 perl 和 DBI 将一个非常大的表从一个 DB2 复制到另一个 [英] copying a very large table from one DB2 to another, using perl and DBI

问题描述

我需要每天将一个非常大(数百万行)的表从一个 DB2 DB 复制到另一个 DB2 DB，并且我需要使用 perl 和 DBI.

I need to copy, on a daily basis, a very large (millions of rows) table from one DB2 DB to another, and I need to use perl and DBI.

有没有比从第一个 DB 中简单地 fetchrow_array 每一行并将它们一个接一个插入第二个 DB 更快的方法?这是我得到的:

Is there a faster way to do this than to simply fetchrow_array each row from the first DB and insert them one-by-one into the second DB? Here's what I got:

$sth1 = $udb1 -> prepare($read_query);
$sth1 -> execute();
$sth1 -> bind_columns((@row{@{$sth1 -> {NAME_1c}}}));

$sth2 = $udb2 -> prepare($write_query);

while ($sth1 -> fetchrow_arrayref) {
    $sth2 -> execute($row{field_name_1}, $row{field_name_2});
}

我从一个类似的线程中实现了一些解决方案，但它仍然很慢.肯定有更好的方法吗?

I implemented some solutions from a similar thread, but it's still slow. Certainly there has to be a better way?

推荐答案

如果你把它包装到一个事务中，它应该工作得更快.使用这样的东西:

If you wrap this into one transaction, it should work much faster. Use something like this:

$sth1 = $udb1->prepare($read_query);
$sth1->execute();
$sth1->bind_columns((@row{@{$sth1->{NAME_1c}}}));

$udb2->begin_work();
$sth2 = $udb2->prepare($write_query);
while ($sth1->fetchrow_arrayref()) {
    $sth2->execute($row{field_name_1}, $row{field_name_2});
}
$udb2->commit();

如果您有数百万行，您可能希望每几千行执行一次提交.

If you have millions of rows, you may want to perform commit every few thousand rows.

现在，它更快的原因:

在您的情况下，每个插入都是一个自动提交的事务.换句话说，服务器必须等到您的更改真正刷新到数百万行中的每一行 - 非常慢！

In your case, every single insert is one auto-committed transaction. In other words, server has to wait until your changes are really flushed to disk for every single of your millions of rows - very SLOW!

当您将其包装到事务中时，服务器可以一次将数千行刷新到磁盘 - 效率更高、速度更快.

When you wrap it into transaction, server can flush thousands of rows to disk at once - much more efficient and faster.

(如果您一遍又一遍地复制完全相同的表，那么通过某种唯一键同步更改会更明智 - 应该快一百万倍).

(If you are copying exact same table over and over again, it would be wiser to synchronize changes by some sort of unique key instead - should be million times faster).

这篇关于使用 perl 和 DBI 将一个非常大的表从一个 DB2 复制到另一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！