挑战极限 Oracle数据库一秒导百万数据
2010年09月07日13:42 来源：李鸣的博客 作者：李鸣 编辑：胡铭娅 评论：2条
本文Tag： Oracle .NET c# 【IT168 技术文档】.Net程序中可以通过ODP调用特性，对Oracle数据库进行操作，今天来讲一下数据批量插入的功能，所用技术不高不深，相信很多朋友都接触过，小弟班门弄斧了，呵呵。这篇文章是上篇文章的续集，因为上一次试验的征集结果没有突破4秒的方法，所以这次继续挑战与挖掘新方法，虽然是Oracle，但仍具有一定收藏意义。
上一次文章中提及的试验： 挑战极限 C#百万数据4秒导入SQL Server
这个试验是针对SQL SERVER数据库的，宿主环境也是.Net，有兴趣的朋友可以将这两个试验对比一下，为日后工作批量导数提供支持。
另外，一些朋友对上次试验环境有些异议，认为应该对数据库和服务器做优化或设置，以体现试验最终的时间结果。这个固然会影响试验的时间结果，但考虑到在试验环境中，对数据库优化的标准与优化程度不便统一与定量，试验结果也不易说明其影响源，所以这次试验依然以标准数据库建库后的配置为主，试验所在服务器硬件环境与上次试验保持一致。实验目的在于挖掘、对比宿主程序中的数据批量操作方法。
● 普通肉垫式
什么叫批量插入呢，就是一次性插入一批数据，我们可以把这批数据理解为一个大的数组，而这些全部只通过一个SQL来实现，而在传统方式下，需要调用很多次的SQL才可以完成，这就是著名的“数组绑定”的功能。我们先来看一下传统方式下，插入多行记录的操作方式：
//设置一个数据库的连接串，
string connectStr = "User Id=scott;
Password=tiger;Data Source=";
OracleConnection conn = new OracleConnection(connectStr);
OracleCommand command = new OracleCommand();
command.Connection = conn; conn.Open();
Stopwatch sw = new Stopwatch();
sw.Start();
//通过循环写入大量的数据，这种方法显然是肉垫
for
(int i = 0; i < recc; i++)
{ string sql = "insert into dept values(" + i.ToString() + "," + i.ToString() + "," + i.ToString() + ")";
https://www.doczj.com/doc/225149393.html,mandText = sql;
command.ExecuteNonQuery();
}
sw.Stop();
System.Diagnostics.Debug.WriteLine("普通插入:" + recc.ToString() + "所占时间:" + sw.ElapsedMilliseconds.ToString());
我们先准备好程序，但是先不做时间的测定，因为在后面我们会用多次循环的方式来计算所占用的时间。
● 使用ODP特性
看上面的程序，大家都很熟悉，因为它没有用到任何ODP的特性，而紧接着我们就要来介绍一个神奇的程序了，我们看一下代码，为了更直观，我把所有的注释及说明直接写在代码里：
//
设置一个数据库的连接串
string connectStr = "User Id=scott;Password=tiger;
Data Source=";
OracleConnection conn = new OracleConnection(connectStr);
OracleCommand command = new OracleCommand();
command.Connection = conn;
//到此为止，还都是我们熟悉的代码，下面就要开始喽 //这个参数需要指定每次批插入的记录数
command.ArrayBindCount = recc;
//在这个命令行中,用到了参数,参数我们很熟悉,但是这个参数在传值的时候
//用到的是数组,而不是单个的值,这就是它独特的地方 https://www.doczj.com/doc/225149393.html,mandText = "insert into dept values(:deptno, :deptname, :loc)";
conn.Open();
//下面定义几个数组,分别表示三个字段,数组的长度由参数直接给出
int[] deptNo = new int[recc];
string[]
dname = new string[recc];
string[] loc = new string[recc];
// 为了传递参数,不可避免的要使用参数,下面会连续定义三个
// 从名称可以直接看出每个参数的含义,不在每个解释了 OracleParameter deptNoParam = new OracleParameter("deptno", OracleDbType.Int32);
deptNoParam.Direction = ParameterDirection.Input;
deptNoParam.Value = deptNo;
command.Parameters.Add(deptNoParam);
OracleParameter deptNameParam = new OracleParameter("deptname", OracleDbType.Varchar2);
deptNameParam.Direction = ParameterDirection.Input;
deptNameParam.Value = dname;
command.Parameters.Add(deptNameParam);
OracleParameter deptLocParam = new OracleParameter("loc", OracleDbType.Varchar2);
deptLocParam.Direction = ParameterDirection.Input;
deptLocParam.Value = loc; command.Parameters.Add(deptLocParam);
Stopwatch sw = new Stopwatch();
sw.Start();
//在下面的循环中,先把数组定义好,而不是像上面那样直接生成SQL
for
(int i = 0; i < recc; i++) { deptNo[i] = i;
dname[i] = i.ToString();
loc[i] = i.ToString();
}
//这个调用将把参数数组传进SQL,同时写入数据库
command.ExecuteNonQuery();
sw.Stop();
System.Diagnostics.Debug.WriteLine("批量插入:" + recc.ToString() + "所占时间:" +sw.ElapsedMilliseconds.ToString());
以上代码略显冗长，但是加上注释后基本也就表达清楚了。
好了，到目前为止，两种方式的插入操作程序已经完成，就剩下对比了。我在主函数处写了一个小函数，循环多次对两个方法进行调用，并且同时记录下时间，对比函数如下：
for (int i = 1; i <= 50; i++) { Truncate(); OrdinaryInsert(i * 1000); Truncate(); BatchInsert(i * 1000); }
当数据量达到100万级别时，所用时间依然令人满意，最快一次达到890毫秒，一般为1秒左右。
经过试验，得出一组数据，可以看出两种方式在效率方面惊人的差距(占用时间的单位为毫秒)，部分数据如下：
记录数 标准 批处理
1000 1545 29
2000 3514 20
3000 3749 113
4000 5737 40
5000 6820 52
6000 9469 72
7000 10226 69
800
0 15280 123
9000 11475 83
10000 14536 121
11000 15705 130
12000 16548 145
13000 18765 125
14000 20393 116
15000 22181 159
因为篇幅原因，不再粘贴全部的数据，但是我们可以看一下由此数据生成的散点图：
其中有些数据有些跳跃，可能和数据库本身有关系，但是大部分数据已经能说明问题了。看了这些数据后，是不是有些心动了?
源程序放了一段时间直接拷贝贴过来了，可能需要调试一下才能跑通，不过不是本质性问题，对了如果要测试别忘记安装Oracle访问组件。
挑战极限 C#百万数据4秒导入SQL Server
2010年09月07日11:12 来源：李鸣的博客 作者：李鸣 编辑：胡铭娅 评论：0条
本文Tag： Sql Server 2000 c# SQL Server SQL Server 2008 【IT168 技术文档】实际工作中有时候需要把大量数据导入数据库，然后用于各种程序计算，本实验将使用5中方法完成这个过程，并详细记录各种方法所耗费的时间。
本实验中所用到工具为VS2008和SQL SERVER 2000、SQL SERVER 2008，分别使用5中方法将100万条数据导入SQL 2000与SQL 2008中，实验环境是DELL 2850双2.0GCPU，2G内存的服务器。感兴趣的朋友可以下载源代码自己验证一下所用时间。
还要有一点需要进行说明，本实验中执行SQL语句的地方使用了IsLine FrameWork框架中的DataProvider模块，这个模块只是对SQL配置的读取和封装，并不会对最终结果有本质性的影响，关于IsLine FrameWork框架方面的知识，请参考“IsLine FrameWork”框架系列文章。
下面进入正题，分别使用基本的Insert 语句、使用BULK INSERT语句、在多线程中使用BULK INSERT、使用SqlBulkCopy类、在多线程中使用SqlBulkCopy类五种方法，挑战4秒极限。
数据库方面使用SQL 2000与SQL 2008，表名TableB，字段名称为Value1，数据库名可以在App.config中修改，默认为test。
图 1 试验中的5种方法
方法一.使用基本的Insert 语句
这种方法是最基本的方法，大多数人一开始都会想到这种方法。但是Insert语句似乎并不适合大批量的操作，是不是这样呢?
本方法中将100万数据分为10个批次，每个批次10万条，每10万条1个事务，分10次导入数据库。
基本语句:Insert Into TableB (Value1) values (‘”+i+”’);
说明：语句中的i是宿主程序中的一个累加变量，用于填充数据库字段中的值。
SQL 2000 耗时：901599
SQL 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 2000耗时：4009
SQL 2008耗时：10722
方法三.在多线程中使用BULK INSERT
在方法二的基础上，将100万条数据分五个线程，每个线程负责20万条数据，每5万条一个事物，五个线程同时启动，看看这样的效果吧。
SQL 2000耗时：21099
SQL 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 2000耗时：4989
SQL 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