让百万级数据瞬间导入SQL Server
想必每个DBA都喜欢挑战数据导入时间,用时越短工作效率越高,也充分的能够证明自己的实力。实际工作中有时候需要把大量数据导入数据库,然后用于各种程序计算,本文将向大家推荐一个挑战4秒极限让百万级数据瞬间导入SQL Server实验案例。
本实验将使用5中方法完成这个过程,并详细记录各种方法所耗费的时间。所用到工具为eclipse和SQL Server 2000、SQL Server 2008,分别使用5中方法将100万条数据导入SQL Server 2000与SQL Server 2008中,实验环境是DELL 2850双2.0GCPU,2G内存的服务器。感兴趣的朋友可以下载源代码自己验证一下所用时间。
好了,下面我们分别使用基本的Insert 语句、使用BULK INSERT语句、在多线程中使用BULK INSERT、使用SqlBulkCopy类、在多线程中使用SqlBulkCopy类五种方法,挑战4秒极限。还要有一点需要进行说明,本实验中执行SQL语句的地方使用了IsLine FrameWork框架中的DataProvider模块,这个模块只是对SQL配置的读取和封装,并不会对最终结果有本质性的影响,关于IsLine FrameWork框架方面的知识,请参考“IsLine FrameWork”框架系列文章。
数据库方面使用SQL Server 2000与SQL Server 2008,表名TableB,字段名称为Value1,数据库名可以在App.config中修改,默认为test。
方法一.使用基本的Insert 语句
这种方法是最基本的方法,大多数人一开始都会想到这种方法。但是Insert语句似乎并不适合大批量的操作,是不是这样呢?
本方法中将100万数据分为10个批次,每个批次10万条,每10万条1个事务,分10次导入数据库。
-->基本语句:
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)
说明:“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); }
说明:
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 SERVER 2008导入数据的性能似乎并不想我们想象的那样优秀。
本文转载技术论坛上的
相关推荐
百万级数据在Excel和SqlServer之间相互导入导出。
将Excel中的数据导入到SqlServer中的实现方法总结。还有一个简单的例子
Excel导入SQLserver源码 Excel是一种非常灵活的电子表格软件,用它可以存储各种数据,本节将对如何将Excel导入SQL Server2000数据库进行详细介绍。 开发环境:NET2.0 开发工具:vs2005 开发语言:c# 数据库:server...
将Excel数据导入到SQLServer中,可以选择要导入的Excel文件和Sheet名称,表名。通过自动编写存储过程来实现导入功能。实际使用无须这么麻烦,可以作为数据导入和存储过程参考例子。
SQL Server数据导入SQLite工具 Convert SQL Server DB to SQLite DB
1.1 SQL Server Management Studio中导入 1.2 直接启动SQL Server导入和导出向导 1.3 SQLServer查询分析器使用代码导入数据 1.4 导入null值数据的说明
将DBF文件导入到SQL server 数据库,并且导出到本地的excel
利用 Microsoft SQL Server Migration Assistant for Oracle.exe(微软数据库迁移工具) ,将oracle数据库迁移数据到SQL server详解。
DataConversion:方便进行Excel的数据向SQL Server数据库的导入工作,适合简单重复性的导入操作,使用XML配置文件进行导入设置的保存,理论上支持数据的任意导入(Update更新、Insert插入),数据库密码做了加密处理,...
使用Asp.Net开发,用于导入Excel文件到SQLServer,支持配置导入字段。支持主键为Int的外键。 两种方式:1、先导入Excel再配置对应关系 2、先配置对应关系在导入
以上就是这几天的实验结果了,比较令人失望的是SQL SERVER 2008导入数据的性能似乎并不想我们想象的那样优秀。 另外,有下载源代码的帮我看看,为什么多线程的成绩还不如单线程的优秀呢?是静态资源使用不当造成的...
XML导入SQLServer工具,配置简单,支持大批量导入到SQLServer。
oracle数据导入sql server ,类型不变,配置简单快速
将Mysql数据库导入到SQLSERVER里面
C# excel 数据导入到sqlserver,方便数据的整理
使用表格的形式,编写数据,并将全部数据一次导入sqlserver中
sql server 导入超大SQL脚本文件.doc 说明
Execl数据如何导入SQL Server 2005
利用C#编程将Excel中的数据导入到Sqlserver数据库中