ADO.Net实现Oracle大批量数据的更新优化

来源：赛迪网    作者：10633

在日常的业务系统应用中，大家经常会使用到大量数据的的提交（包括查询、更新或删除），假如目标数据库的数据量较大，一次需要处理的操作较多，就会出现系统执行效率低下等问题。文本中笔者以Oracle9i数据库为例，通过对ADO.Net中的数据库支持的应用实践，说明几种常见的优化处理方法，并对比其中的优劣。

为了更详细说明情况，笔者以某业务数据填报功能为例，假设有100个用户每周需要填报某统计数量，填报明细的数据量约为200条，有专门的填报页面实现一次提交，这样一周的数据增量约为2万，一年为100多万，要保证系统有效运行6年以上，需要考虑数据存储（增、删、改）效率问题，（数据库本身的优化配置，包括表空间、索引等查询效率已经考虑，不在此讨论范畴）。这类业务的特点是，数据操作量较大，但执行的指令复杂度较低，包含简单的新增、修改、删除3类。

传统处理方法存在的问题

对每一个要处理的操作，直接对目标表执行对应的SQL操作（或存储过程），可使用ADO.Net的参数化SQL或通过DataSet与DataAdapter来间接处理。这样每个用户批量提交数据时，需要执行大约200次SQL操作，虽然数据库进行了优化，单次执行SQL的效率并不低，但由于一次执行的指令较多，随着目标数据容量的增加，效率会逐步降低，最终不可忍受。

优化方法1：临时表处理模式

对于大规模的目标数据库表，进行多次修改、删除或更新操作，效率必定较慢，要降低对目标表的操作次数，可以采用临时表的解决办法。具体方法为：建立一个与目标表结构类似的临时表（由于B/S模式的特点，临时表是基于事务的，而不是基于连接的），并增加操作模式标记字段，在执行操作前，将本次要操作的数据，就是某个用户，每周的数据（约200条左右，第一次处理时应该没有数据）一次查询转入临时表，再对临时表执行修改、更新、删除（作删除标记）操作，处理完毕后，分别将临时表的数据分三类提交到目标表。流程如下图所示：

删除
Delete From TARGET_TABLE Where KEY In(Select KEY 

From  TEMP_TABLE Where STATE=’Delete’
 
新增
Insert Into TARGET_TABLE … 

Select …  From TEMP_TABLE Where STATE=’Insert’
 
修改
Update TARGET_TABLE Set …

Where KEY=TEMP_TABLE.KET AND  TEMP_TABLE.STATE=’Update’

实验证明，在50万数据量的条件下，此方法能比传统的方法快40倍左右，且执行效率受目标数据库容量的影响较小。

优化方法2：使用SQL批处理

SQL批处理一般有2种模式：一种是将要执行的SQL语句，连接形成批处理指令，一次提交到服务器执行；一种是对执行的SQL指令，传递多组参数，批执行。这两种方法都需要数据库及ADO.Net的支持。

System.Data.OracleClient 的ADO.Net 2.0版本支持第一种方式的的批处理，如通过DataAdapter对DataSet的批量数据提交时，系统会根据数据集合中的新增，修改，删除标识，构造批处理指令，形成SQL指令段，提交服务器执行。这种方式是将多个SQL指令形成一组SQL指令的方法，实现多个指令的批执行，能一定程度提高功能的执行效率。原理如下所示：

Begin  

Insert Into TAREGT_TABLE(A,B,C) Values(:1,:2,:3);

Insert Into TAREGT_TABLE(A,B,C) Values(:4,:5,:6);

Insert Into TAREGT_TABLE(A,B,C) Values(:7,:8,:9);

……

Insert Into TAREGT_TABLE(A,B,C) Values(:n,:n+1,:n+2);

end;

此方法形成的批处理SQL指令及参数会随着数据量的增加而成倍增加，数据更新量与执行效率受到限制。而微软的Oracle ADO.Net实现并没有将批处理方法直接对外公开，只能通过DataSet的数据批量更新间接使用。

另一种处理方法是使用Oracle的ADO.Net实现。Oracle.DataAccess.Client实现的ADO.Net支持第二种模式的批处理指令，其利用Oracle数据库自带的批处理功能，通过设定OracleCommand的ArrayBindCount来实现对参数数组的传递。当ArrayBindCount设置为大于1时，传递给一个OracleCommand的参数不再是参数值，而是参数数组，这样，一条Command指令就可以执行多个处理，如：插入100条数据。使用这种方法，利用了数据库本身对批量数据操作的优化机制，极大提高了数据操作效率。通过对目标数据库容量为50万的目标表测试发现，此方法执行比传统方法的执行效率提高50倍以上，在测试过程中发现，100万的目标数据量的情况下，一次插入1万条数据，只需要1秒左右，且操作效率受目标数据量的影响较小。

总结

通过以上的分析，我们可以得出以下结论，采用临时表的方法及批处理的手段都能较好解决大规模数据量模式下的批量数据提交的问题。其中，Oracle的ADO.Net的实现效率最高，处理最简单，微软ADO.Net2.0的实现没有完全利用数据库本身的功能，功能及效率受到局限。而临时表的处理方法编程比较复杂，适合于在使用微软的ADO.Net的情况下使用。更进一步，Oracle存储过程支持参数数组的传递，也可以采用通过传递参数数组的存储过程来实现，前提也是必须采用Oracle的ADO.Net实现，因为微软的ADO.Net实现不支持参数数组传递；而Oracle数据库也支持Bulk Insert功能，如果有批量的数据需要插入，可以考虑使用此方法，此处不详细讨论。

这种方法将对目标数据库表的200次SQL操作，转化为对临时表的数据库操作，由于临时表数据量少，效率较高且周期稳定，而最后的数据更新，只涉及到临时表到目标表的有限的3次SQL操作，不涉及到ADO.Net与数据库的数据交互，效率相对较高。