65.9K
CodeProject 正在变化。 阅读更多。
Home

处理从 CSV 到 SQL Server 的批量数据插入

Ashrafur Rahaman
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.69/5 (16投票s)

2012年8月13日

CPOL

5分钟阅读

viewsIcon

393471

通过本文，开发人员将获得一些关于在 SQL Server 中批量插入数据的有用指南。

引言

我撰写本文是基于将大量数据（约 500 万行，400 MB）从 CSV 文件插入到 SQL Server 数据库的经验。

背景 

有几种方法可以从 CSV 文件将大量数据插入到数据库；我们的目标是执行更快的插入，并从 C# 应用程序执行插入。为了实现这一目标，我们试验了一些常见的、广为人知的批量数据插入处理技术。以下是我们试验过的技术：

  1. SQL BULK INSERT 查询
  2. BCP 或 SqlBulkCopy 库，用于使用 C# 或 VB 插入大量数据
  3. SQL Server Integration Service (SSIS)
  4. C# 或 VB 中的普通 SQL 命令库

通过本文，开发人员将获得一些关于在 SQL Server 中批量插入数据的有用指南。

1. SQL BULK Insert

使用 BULK INSERT 语句，我们可以直接从 CSV 文件将大量数据插入到数据库。BULK INSERT 查询的最简单形式如下：

BULK INSERT dbo.TableForBulkData
FROM 'C:\BulkDataFile.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

为了处理事务和回滚，可以使用 try-catch 块，如下所示：

EGIN TRANSACTION
BEGIN TRY
BULK INSERT dbo.BulkDataTable
FROM 'C:\TestFiles\Bulk3.csv'
WITH
(
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    ROWS_PER_BATCH = 10000, 
    TABLOCK
)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH

有关 BULK INSERT SQL 的详细信息，请访问：http://msdn.microsoft.com/en-us/library/ms188365.aspx。

BULK INSERT 实际上是一个丰富的 T-SQL 命令，它接受参数来设置错误、批次大小、触发器等。

2. BCP 或 SqlBulkCopy 库

.NET Framework 在 System.Data.SqlClient 命名空间中包含 SqlBulkCopy 类，该类可以轻松高效地将大量数据从 .NET 应用程序复制到 SQL Server 数据库。

有关 SqlBulkCopy 操作的详细信息，请访问：http://msdn.microsoft.com/en-us/library/tchktcdk%28v=vs.80%29.aspx。

SqlBulkCopy 类将大量数据从数据表复制到数据库表。SqlBulkCopy 接受以下类型的参数来将数据复制到数据库：System.Data.DataRow[]、System.Data.DataTable、System.Data.IDataReader。 

现在的挑战是将大型 CSV 文件转换为这些数据类型之一：DataRow[]、DataTable、IDataReader。有一些开源库可用于执行此类转换。为了进行试验，我们使用了 CSVReader（https://codeproject.org.cn/Articles/9258/A-Fast-CSV-Reader），它通过 System.Data.IDataReader 接口绑定 CSV 文件中的数据。

以下代码可以完美地执行从 CSV 到数据库表的 SqlBulkCopy 操作。

StreamReader file = new StreamReader(bulk_data_filename);
CsvReader csv = new CsvReader(file, true,',');
SqlBulkCopy copy = new SqlBulkCopy(conn);
copy.DestinationTableName = tablename;
copy.WriteToServer(csv);

默认情况下，批量复制操作在其自己的事务中运行。要提交或回滚，需要包含一个事务。以下是带有事务的 SqlBulkCopy 的代码。 

SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
SqlTransaction transaction = conn.BeginTransaction();
try
{
    using (StreamReader file = new StreamReader(filename))
    {
        CsvReader csv = new CsvReader(file, true, '|');
        SqlBulkCopy copy = new SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity, transaction);
        copy.DestinationTableName = tablename;
        copy.WriteToServer(csv);
        transaction.Commit();
    }
}
catch (Exception ex)
{
    transaction.Rollback();
}
finally
{
    conn.Close();
}

对于非常大的 CSV 文件，最好添加 BulkCopyTimeout：copy.BulkCopyTimeout = XXX;。

3. SQL Server Integration Service

SQL Server 包含一个强大的数据集成和转换应用程序，称为 SQL Server Integration Service (SSIS)。SSIS 的主要功能之一是将数据从几乎任何格式的外部数据源移动到 SQL Server。使用 Business Intelligent Development Studio (BIDS)，我们可以轻松地将数据从 CSV 文件导入到数据库。此外，将包文件设置为自动定期作业也非常简单。

以下是创建 SSIS 服务包以从 CSV 文件导入数据到 SQL Server 的基本步骤：

  1. 打开 SQL Server Business Intelligence Studio。
  2. 创建一个新的“Integration Service Project”。 
  3. 在“Control Flow”选项卡中，从工具箱拖动一个“Data Flow Task”。
  4. 转到“Data Flow”选项卡。
  5. 在“Data Flow”页面中，从工具箱拖动“Flat File Source”和“ADO.NET Destination”并进行设置。
  6. 将 Flat File Source 的输出路径（绿色箭头）连接到 ADO.NET Destination。

运行创建的 SSIS 包会将 CSV 文件中的数据复制到 SQL 数据库。 

4. 使用传统的 SQLCommand 类插入数据

可以使用传统的 SQLCommand 类从 CSV 文件将数据插入到数据库。但这一个过程非常慢。与我之前讨论过的其他三种方法相比，这种方法至少慢 10 倍。强烈建议不要逐行遍历 CSV 文件并为每一行执行 SqlCommand 来将大量数据从 CSV 文件插入到 SQL Server 数据库。

比较分析

在我们的研究中，我们发现 BULK Insert SQL 和 SQLBulkCopy 表现最好。对于大约 400MB 的 CSV 文件，数据插入平均需要三分钟。SQL Server Integration Service (SSIS) 比 BULK Insert 和 SQLBulkCopy 慢一倍左右。普通数据插入花费的时间非常长。

根据我们的结果，我们可以说使用 BULK Insert SQL 或 SQLBulkCopy 类在处理大量数据插入方面表现最佳。我们也可以使用 SSIS 进行批量数据插入，但使用普通的 SQLCommand 插入大量数据不是一个合理的解决方案。 

注意：结果可能因系统而异。

专家意见（参考资料，用于改进）

根据 Adrian Hills 的说法：如果您使用 SqlBulkCopy 类，通过 ADO.NET 进行批量加载可以非常高效。如果您的源数据是 XML，您可以尝试将其加载到 DataSet 中，方法是手动遍历 XML 文档，或通过 DataSet.ReadXml 方法。然后，通过以下方式高度优化使用 SqlBulkCopy 类进行批量加载：

  • 加载到堆表（无索引 - 在数据加载完成后创建索引）
  • 为批量加载指定 TableLock 选项，这将为表获取一个批量上传锁

论坛建议：http://social.msdn.microsoft.com/Forums/en/transactsql/thread/812b9a6a-541d-4d3a-b7a0-005b93012264。

相关博客：http://www.adathedev.co.uk/2010/02/sqlbulkcopy-bulk-load-to-sql-server.html

Greg Robidoux 在 http://www.mssqltips.com/sqlservertip/1185/minimally-logging-bulk-load-inserts-into-sql-server/ 中写了关于最小化 SQL Server 批量加载插入日志记录的技巧。

优化批量导入性能

以下是优化批量导入性能的建议：

  • 使用最小日志记录
  • 从多个客户端并行将数据导入到单个表中
  • 禁用触发器 
  • 禁用约束
  • 对数据文件中的数据进行排序
  • 控制锁定行为
  • 以原生格式导入数据

有关这些优化建议的详细信息，请访问：http://msdn.microsoft.com/en-us/library/ms190421%28v=sql.105%29.aspx。

历史

V01 - 2012 年 8 月 13 日 - 初始版本。

© . All rights reserved.