实战手记：让百万级数据瞬间导入SQL Server

想必每个DBA都喜欢挑战数据导入时间。时间越短，工作效率越高，充分证明自己的实力。实际操作中，有时需要将大量数据导入数据库，然后用于各种程序计算。本文将推荐一个实验案例，挑战4秒钟的限制，并允许数百万数据立即导入sql server。

本实验将使用5中的方法来完成这个过程，并详细记录每种方法所花费的时间。使用的工具有visual studio 2008、sql server 2000和sql server 2008。一百万条数据分别通过五种方法导入到SQL Server 2000和SQL Server 2008中。实验环境是DELL 2850服务器，2.0GCPU，2G内存。感兴趣的朋友可以下载源代码自己验证一下时间。

好了，我们用五种方法来挑战4秒的极限：基本Insert语句，BULK INSERT语句，多线程中的BULK INSERT，多线程中的SqlBulkCopy类和SqlBulkCopy类。还有一点需要说明。在这个实验中，在执行SQL语句的地方使用了IsLine FrameWork框架中的DataProvider模块。该模块只读取和封装SQL配置，不会对最终结果产生本质影响。关于IsLine框架框架的知识，请参考关于IsLine框架框架的系列文章。

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

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

这种方法是最基本的一种，大多数人一开始都会想到。但是Insert语句好像不太适合批量操作吧？

该方法将100万条数据分成10批，每批10万条，每10万条有一笔交易，分10次导入数据库。

-基本声明：

向TableB (Value1)中插入值(“I”)；注意：语句中的I是宿主程序中的一个累加变量，用来填充数据库字段中的值。

SQL Server 2000时间：901599

SQL Server 2008耗时：497638

方法二。使用BULK INSERT语句

这堂课的效果可以说是本次实验中最令人满意的。这是最简单，灵活和快速使用。

“BULK INSERT”语句似乎不太常用。Aicken听说oracle中有一种方法，可以将外部文件映射到Oracle临时表中，然后直接将临时表中的数据导入到其他Oracle表中。这种方法的速度非常令人满意。SQL SERVER的大容量插入同样令人满意吗？

-基本声明：

'大容量插入TableB FROM '

c:\ \ SQL . txt ' WITH(field terminator=''ROWTER

/.mbMINATOR='| 'BATCHSIZE=100000)

解释：“c:\\sql.txt”是一个预先生成的文件，包含100条数据，用“|”符号分隔，每100，000条数据有一个事务。

SQL Server 2000时间：4009

SQL Server 2008时间：10722

方法三。在多线程中使用批量插入

在第二种方法的基础上，把100万条数据分成5个线程，每个线程负责20万条数据，每5万条数据有一件事。来看看效果吧。

SQL Server 2000时间：21099

SQL Server 2008时间：10997

方法四。使用SqlBulkCopy类

这个方法也很快，但是要靠记忆。对于数千万条包含多个字段的复杂数据，可能会消耗大量内存，但可以使用64位解决方案来处理这个问题。

几千万条多字段数据的情况，一般在一些业务场景中都会遇到。例如，在计算某个业务周期内全局消费者的消费时，需要先获取主数据库表中成员消费记录的快照，并将快照存储在临时表中，然后将数据用于计算程序。而且有时候消费者的消费数据并不在单个的数据库服务器中，而是来自多个国家的多个服务器，所以我们要借助内存或者外部存储设备来转移这些数据，然后进行清洗、合并、检测，最后导入专门的表格供计算程序使用。

基本声明：

使用(系统。data . sqlclient . SQL bulkcopy SQL BC

=新系统。data . sqlclient . SQL bulkcopy(conn))

{ sqlBC。BatchSize=100000sqlBC。批量复制超时

=60;sqlBC。DestinationTableName='dbo。表b '

sqlBC。ColumnMappings.Add('valueA '' value 1 ')；

sqlBC。write to server(dt)；}

描述：

BatchSize=100000指示每100，000个项目进行一次交易并提交。

BulkCopyTimeout=60表示60秒将超时。

DestinationTableName='dbo。表b '指示将数据导入TableB表

列映射。Add('valueA '' value 1 ')；指示内存中的valueA字段与TableB中的Value1字段相匹配。

write to server(dt)；写入数据库。其中dt是预构建的DataTable，它包含valueA字段。

SQL Server 2000时间：4989

SQL Server 2008时间：10412

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

基于方法四，把100万条数据分成5个线程，每个线程负责20万条数据，每5万条数据有一件事。来看看效果吧。

SQL 2000耗时：7682

SQL 2008时间消耗：10870

结果

实验终于在几天后完成了。令人失望的是，SQL SERVER 2008中导入数据的性能似乎没有我们想象的那么优秀。