C＃优化：向数据库插入2亿行 [英] C# Optimisation: Inserting 200 million rows into database

问题描述

我有以下（简化的）代码，我想针对速度进行优化：

I have the following (simplified) code which I'd like to optimise for speed:

long inputLen = 50000000; // 50 million 
DataTable dataTable = new DataTable();
DataRow dataRow;
object[] objectRow;
while (inputLen--)
{
    objectRow[0] = ...
    objectRow[1] = ...
    objectRow[2] = ...

    // Generate output for this input
    output = ...

    for (int i = 0; i < outputLen; i++) // outputLen can range from 1 to 20,000
    {
         objectRow[3] = output[i];
         dataRow = dataTable.NewRow();
         dataRow.ItemArray = objectRow;
         dataTable.Rows.Add(dataRow);
    }
}

// Bulk copy
SqlBulkCopy bulkTask = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock, null);
bulkTask.DestinationTableName = "newTable";
bulkTask.BatchSize = dataTable.Rows.Count;
bulkTask.WriteToServer(dataTable);
bulkTask.Close();

我已经在使用SQLBulkCopy试图加快速度，但似乎为数据表本身被证明很慢。

I'm already using SQLBulkCopy in an attempt to speed things up, but it appears assigning values to the DataTable itself proves to be slow.

我不知道数据表如何工作，所以我想知道是否通过先创建可重用的数组来创建不必要的开销？将其分配给DataRow，然后将DataRow添加到DataTable？还是首先使用DataTable并不是最佳选择？输入来自数据库。

I don't know how DataTables work so I'm wondering if I'm creating unnecessary overhead by first creating a reusable array, then assigning it to a DataRow, then adding the DataRow to the DataTable? Or is using DataTable not optimal in the first place? The input comes from a database.

我不太关心LOC，只是速度。有人可以为此提供建议吗？

I don't care much about LOC, just speed. Can anyone give some advice on this?

推荐答案

对于这么大的表，您应该使用$
$ b

For such a big table, you should instead use the

public void WriteToServer(IDataReader reader)

方法。

这可能意味着您必须实现自己的伪 IDataReader 接口使用代码（如果您没有从现有的 IDataReader 获取数据），但是通过这种方式，您将获得从头到尾的流式传输，并且避免2亿循环。

It may mean you'll have to implement yourself a "fake" IDataReader interface with your code (if you' don't get the data from an existing IDataReader), but this way, you'll get "streaming" from end to end, and will avoid a 200 million loop.

这篇关于C＃优化：向数据库插入2亿行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！