使用foreach循环将数据批量复制到SQL Server

Question

我有一个c#控制台应用程序，它从CSV文件中读取百万源数据，并将它们批量插入SQL Server。

我将数据按1000计数分组，并使用foreach循环分组。每个循环创建一个新的SqlConnection和一个新的SqlBulkCopy对象，并在循环结束时处理它们。

for (int index = 0; index < dts.Count; index++)
{
DataTable _dt = dts[index];
try
{
using (SqlConnection connection = new SqlConnection(conn))
{
await connection.OpenAsync();
//using (SqlTransaction trans = connection.BeginTransaction())
using (CancellationTokenSource cts = new CancellationTokenSource())
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName = "[dbo].[XXXX]";
bulkCopy.BatchSize = 1000;
bulkCopy.WriteToServer(_dt);
}
}
}
}                

前10组在1中工作得很快。X秒，但之后每组需要4到60秒。

Answer 1

如果您使用处理器的其他核心，您将使用稍微快一点的速度。使用线程按核数划分数据可以节省时间。

Thread t1 = new Thread(new ThreadStart(Thread1));

Thread t2 = new Thread(new ThreadStart(Thread2));
t1.Start();
t2.Start();

int piece = dts.Count / 2;
public static void Thread1()
{
for (int index = 0; index < piece; index++)
{
DataTable _dt = dts[index];
try
{
using (SqlConnection connection = new SqlConnection(conn))
{
await connection.OpenAsync();
//using (SqlTransaction trans = connection.BeginTransaction())
using (CancellationTokenSource cts = new CancellationTokenSource())
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName = "[dbo].[XXXX]";
bulkCopy.BatchSize = 1000;
bulkCopy.WriteToServer(_dt);
}
}
}
}
}
public static void Thread2()
{
for (int index = piece; index < dts.Count; index++)
{
DataTable _dt = dts[index];
try
{
using (SqlConnection connection = new SqlConnection(conn))
{
await connection.OpenAsync();
//using (SqlTransaction trans = connection.BeginTransaction())
using (CancellationTokenSource cts = new CancellationTokenSource())
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName = "[dbo].[XXXX]";
bulkCopy.BatchSize = 1000;
bulkCopy.WriteToServer(_dt);
}
}
}
}
}

如果你只想要foreach

foreach(DataTable _dt in dts){
try
{
using (SqlConnection connection = new SqlConnection(conn))
{
await connection.OpenAsync();
//using (SqlTransaction trans = connection.BeginTransaction())
using (CancellationTokenSource cts = new CancellationTokenSource())
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName = "[dbo].[XXXX]";
bulkCopy.BatchSize = 1000;
bulkCopy.WriteToServer(_dt);
}
}
}            
}