挑战 SQL Server 四秒导入百万级数据

文章评分 0 次，平均分 0.0 ：

[收起] 文章目录

方法一：使用基本的 Insert 语句
方法二：使用 BULK INSERT 语句
方法三：在多线程中使用 BULK INSERT
方法四：使用 SqlBulkCopy 类
方法五：在多线程中使用SqlBulkCopy类
总结

对于大数据量的导入，是 DBA 们经常会碰到的问题，在这里我们讨论的是 SQL Server 环境下百万级数据量的导入，希望对大家有所帮助。

实际工作中有时候需要把大量数据导入数据库，然后用于各种程序计算，本实验将使用5中方法完成这个过程，并详细记录各种方法所耗费的时间。

本实验中所用到工具为 Visual Studio 2008 和 SQL Server 2000、SQL Server 2008，分别使用 5 中方法将 100 万条数据导入 SQL Server 2000 与 SQL Server 2008 中，实验环境是 DELL 2850 双 2.0G CPU，2G 内存的服务器。感兴趣的朋友可以下载源代码自己验证一下所用时间。

还要有一点需要进行说明，本实验中执行 SQL 语句的地方使用了 IsLine FrameWork 框架中的 DataProvider 模块，这个模块只是对 SQL 配置的读取和封装，并不会对最终结果有本质性的影响，关于IsLine FrameWork 框架方面的知识，请参考 “IsLine FrameWork” 框架系列文章。

下面进入正题，分别使用基本的Insert 语句、使用BULK INSERT语句、在多线程中使用BULK INSERT、使用SqlBulkCopy类、在多线程中使用SqlBulkCopy类五种方法，挑战4秒极限。

数据库方面使用 SQL Server 2000 与 SQL Server 2008，表名 TableB，字段名称为 Value1，数据库名可以在 App.config 中修改，默认为 test。

基本：Insert into
单线程 BulkCopy
多线程 BulkCopy
单线程 BulkInsert
多线程 BulkInsert

方法一：使用基本的 Insert 语句

这种方法是最基本的方法，大多数人一开始都会想到这种方法。但是 Insert 语句似乎并不适合大批量的操作，是不是这样呢?

本方法中将100万数据分为10个批次，每个批次10万条，每10万条1个事务，分10次导入数据库。

Insert Into TableB (Value1) values (‘”+i+”’);

1	Insert Into TableB (Value1) values (‘”+i+”’);

说明：语句中的i是宿主程序中的一个累加变量，用于填充数据库字段中的值。

测试结果：

SQL Server 2000 耗时：901599
SQL Server 2008 耗时：497638

方法二：使用 BULK INSERT 语句

这个类的效果，在本实验中可以说是最令人满意的了，它的使用最简便、灵活，速度很快。

“BULK INSERT” 语句似乎不是很常用， Aicken 听说 Oracle 中有一种可以将外部文件映射为 Oracle 临时表，然后直接将临时表中的数据导入 Oracle 其他表中的方法，这种方法的速度非常令人满意，SQL SERVER的 BULK INSERT 是不是同样令人满意呢?

BULK INSERT TableB FROM 'c:\\sql.txt' WITH (FIELDTERMINATOR = ',',ROWTER /.,mbMINATOR='|',BATCHSIZE = 100000)

1	BULK INSERT TableB FROM 'c:\\sql.txt' WITH (FIELDTERMINATOR = ',',ROWTER /.,mbMINATOR='\|',BATCHSIZE = 100000)

说明：“c:\\sql.txt”是一个预先生成的包含 100 条数据的文件，这些数据以 “|” 符号分隔，每 10 万条数据一个事务。

测试结果：

SQL Server 2000 耗时：4009
SQL Server 2008 耗时：10722

方法三：在多线程中使用 BULK INSERT

在方法二的基础上，将 100 万条数据分五个线程，每个线程负责 20 万条数据，每 5 万条一个事物，五个线程同时启动，看看这样的效果吧。

测试结果：

SQL Server 2000耗时：21099
SQL Server 2008耗时：10997

方法四：使用 SqlBulkCopy 类

这种方法速度也很快，但是要依赖内存，对于几千万条、多字段的复杂数据，可能在内存方面会有较大的消耗，不过可以使用64位解决方案处理这个问题。

几千万条、多字段的数据的情况一般在一些业务场景中会遇到，比如计算全球消费者某个业务周期消费额时，要先获得主数据库表中的会员消费记录快照，并将快照储存至临时表中，然后供计算程序使用这些数据。并且有些时候消费者的消费数据并不在一台数据库服务器中，而是来自多个国家的多台服务器，这样我们就必须借助内存或外存设备中转这些数据，然后清洗、合并、检测，最后导入专用表供计算程序使用。

using (System.Data.SqlClient.SqlBulkCopy sqlBC = new System.Data.SqlClient.SqlBulkCopy(conn))
{
sqlBC.BatchSize = 100000;   
sqlBC.BulkCopyTimeout = 60;   
sqlBC.DestinationTableName = "dbo.TableB";   
sqlBC.ColumnMappings.Add("valueA", "Value1");   
sqlBC.WriteToServer(dt);   
}

using (System.Data.SqlClient.SqlBulkCopy sqlBC = new System.Data.SqlClient.SqlBulkCopy(conn))

{

sqlBC.BatchSize = 100000;

sqlBC.BulkCopyTimeout = 60;

sqlBC.DestinationTableName = "dbo.TableB";

sqlBC.ColumnMappings.Add("valueA", "Value1");

sqlBC.WriteToServer(dt);

}

说明：

BatchSize = 100000; 指示每10万条一个事务并提交
BulkCopyTimeout = 60; 指示60秒按超时处理
DestinationTableName = "dbo.TableB"; 指示将数据导入TableB表
ColumnMappings.Add("valueA", "Value1"); 指示将内存中valueA字段与TableB中的Value1字段匹配
WriteToServer(dt);写入数据库。其中dt是预先构建好的DataTable，其中包含valueA字段。

测试结果：

SQL Server 2000 耗时：4989
SQL Server 2008 耗时：10412

方法五：在多线程中使用SqlBulkCopy类

基于方法四，将100万条数据分五个线程，每个线程负责20万条数据，每5万条一个事物，五个线程同时启动，看看这样的效果吧。

测试结果：

SQL 2000 耗时：7682
SQL 2008 耗时：10870

总结

测试方式	SQL 2000	SQL 2008
基本 Insert into	901599	497638
单线程 Bulk Insert	4209	10722
多线程 Bulk Insert	21099	10997
单线程 SqlBulkCopy	4989	10412
多线程 SqlBulkCopy	7682	10870