百万条插入：SqlBulkCopy超时问题

Question

我们已经有一个运行中的系统，处理所有连接字符串（db2、oracle、MSServer）。

目前，我们正在使用ExecuteNonQuery()进行一些插入操作。

我们想通过使用SqlBulkCopy()而不是ExecuteNonQuery()来提高性能。我们有一些客户端拥有超过5000万条记录。

我们不想使用SSIS，因为我们的系统支持多个数据库。

我创建了一个示例项目来测试SqlBulkCopy()的性能。我为MSServer创建了一个简单的读取和插入函数。

以下是这个小函数：

public void insertIntoSQLServer()
{
    using (SqlConnection SourceConnection = new SqlConnection(_sourceConnectionString))
    {
        //Open the connection to get the data from the source table
        SourceConnection.Open();
        using (SqlCommand command = new SqlCommand("select * from " + _sourceSchemaName + "." + _sourceTableName + ";", SourceConnection))
        {
            //Read from the source table
            command.CommandTimeout = 2400;
            SqlDataReader reader = command.ExecuteReader();

            using (SqlConnection DestinationConnection = new SqlConnection(_destinationConnectionString))
            {
                DestinationConnection.Open();
                //Clean the destination table
                new SqlCommand("delete from " + _destinationSchemaName + "." + _destinationTableName + ";", DestinationConnection).ExecuteNonQuery();

                using (SqlBulkCopy bc = new SqlBulkCopy(DestinationConnection))
                {
                    bc.DestinationTableName = string.Format("[{0}].[{1}]", _destinationSchemaName, _destinationTableName);
                    bc.NotifyAfter = 10000;
                    //bc.SqlRowsCopied += bc_SqlRowsCopied;
                    bc.WriteToServer(reader);
                }
            }
        }
    }
}

当我的dummyTable记录少于200,000条时，批量复制功能正常工作。但是，当记录超过200,000条时，我会遇到以下错误：

• 试图在具有未完成操作的对象上调用批量复制。

或者

• 等待操作超时（对于IDataReader）

我增加了阅读器的CommandTimeout。它似乎已解决与IDataReader相关的超时问题。

我的代码有什么问题吗？

- billybob

---

永远不要使用SqlBulkCopy到目标表，它会严重破坏锁定代码，特别是在使用多线程时。创建一个临时表，将数据插入其中，然后复制到目标表。 - TomTom

我没有使用多线程。 我总是插入到一个空表中。 - billybob

为什么要使用sqlbulkcopy？说真的，如果表在同一个数据库中，只需告诉服务器复制数据即可，而不是将其拉到程序中再上传。使用一条语句直接选择目标表。 - TomTom

它们不会在同一台服务器上...我将从多个数据库中读取，如db2、oracle、sql server、mysql。目前我们正在使用你所说的方式。但由于我们需要支持多个数据库，我们正在探索新的可能性。 - billybob

Answer 1

在调用WriteToServer之前，您可以尝试添加以下内容...

bc.BatchSize = 10000;
bc.BulkCopyTimeout = 0;

我不知道默认的批处理大小或超时时间是多少，但我怀疑这可能是您的问题。希望能帮到你。

此外，您可以尝试使用不同的批处理大小以获得最佳性能。

- Spock

---

在为BulkCopyTimeout提供示例时，请指定0的含义（无限大）。 - JJS

默认的 BatchSize 是零（每个 WriteToServer 是单独的批处理），默认的 BulkCopyTimeout 是 30 秒。 - Matt Kemp

Answer 2

您可以尝试这个。

bc.BatchSize = 100000; // How many Rows you want to insert at a time
bc.BulkCopyTimeout = 60; // Time in Seconds. If you want infinite waiting Time then assign 0.

- Deepesh

---

在 C# 整数字面量中，逗号无效。 - JJS

下划线“_”可以在无需千分位分隔符的情况下，帮助提高可读性。请注意，它只是用来代替千分位分隔符，不要改变原文意思。 - Tom Lint