.Net程序中可以通过ODP调用特性,对Oracle数据库进行操作,今天来讲一下数据批量插入的功能,所用技术不高不深,相信很多朋友都接触过。
(1)普通肉垫式
什么叫批量插入呢,就是一次性插入一批数据,我们可以把这批数据理解为一个大的数组,而这些全部只通过一个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() + ")";
command.CommandText = sql;
command.ExecuteNonQuery();
}
sw.Stop();
System.Diagnostics.Debug.WriteLine("普通插入:" + recc.ToString()
+ "所占时间:" + sw.ElapsedMilliseconds.ToString());
我们先准备好程序,但是先不做时间的测定,因为在后面我们会用多次循环的方式来计算所占用的时间。
(2)使用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;
//在这个命令行中,用到了参数,参数我们很熟悉,但是这个参数在传值的时候
//用到的是数组,而不是单个的值,这就是它独特的地方
command.CommandText = "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());
以上代码略显冗长,但是加上注释后基本也就表达清楚了。
当数据量达到100万级别时,所用时间依然令人满意,最快一次达到890毫秒,一般为1秒左右。
好了,到目前为止,两种方式的插入操作程序已经完成,就剩下对比了。我在主函数处写了一个小函数,循环多次对两个方法进行调用,并且同时记录下时间,对比函数如下:
for (int i = 1; i <= 50; i++)
{
Truncate();
OrdinaryInsert(i * 1000);
Truncate();
BatchInsert(i * 1000);
}
经过试验,得出一组数据,可以看出两种方式在效率方面惊人的差距(占用时间的单位为毫秒),部分数据如下:
记录数 |
标准 |
批处理 |
1000 |
1545 |
29 |
2000 |
3514 |
20 |
3000 |
3749 |
113 |
4000 |
5737 |
40 |
5000 |
6820 |
52 |
6000 |
9469 |
72 |
7000 |
10226 |
69 |
8000 |
15280 |
123 |
9000 |
11475 |
83 |
10000 |
14536 |
121 |
11000 |
15705 |
130 |
12000 |
16548 |
145 |
13000 |
18765 |
125 |
14000 |
20393 |
116 |
15000 |
22181 |
159 |
因为篇幅原因,不再粘贴全部的数据,但是我们可以看一下由此数据生成的散点图:
其中有些数据有些跳跃,可能和数据库本身有关系,但是大部分数据已经能说明问题了。看了这些数据后,是不是有些心动了?
源程序放了一段时间直接拷贝贴过来了,可能需要调试一下才能跑通,不过不是本质性问题,对了如果要测试别忘记安装Oracle访问组件。
新闻热点
疑难解答