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


 在日常的业务系统应用中,大家经常会使用到大量数据的的提交(包括查询、更新或删除),假如目标数据库的数据量较大,一次需要处理的操作较多,就会出现系统执行效率低下等问题。

文本中笔者以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与数据库的数据交互,效率相对较高

本文作者:



相关阅读:
SQL SERVER性能优化综述(很好的总结,不要错过哦)
Linux下玩QQ:OpenQ 编译和安装
Linux系统下管道命令的结合使用技巧
PHP的FTP学习(一)
JavaScript 原型学习总结
在线编辑器的实现原理(兼容IE和FireFox)
ORACLE 10G使用Exp程序导出整个数据库
web前端开发经验浅谈:分享看过的书籍和成长过程
HTML5与数据库API核心规范
div+css与xhtml+css是什么意思?
Javascript中的浅拷贝和深拷贝
语义化的网页 XHTML语义化标记
js单行消息滚动代码,可添加无数个
关于Mozilla浏览器不支持innerText的解决办法
快速导航
PHP MySQL HTML CSS JavaScript MSSQL AJAX .NET JSP Linux Mac ASP 服务器 SQL jQuery C# C++ java Android IOS oracle MongoDB SQLite wamp 交通频道 作文范文 雪花拼图真好玩 关于档案与民生征文范文精选 二年级试卷:小学语文第四册期末试卷(二) 小学六年级作文650字:班级趣事 2 琼瑶不是好老师 真诚就像露天下种的蔬菜 炒板栗 小学五年级作文400字:人生路上的好老师 好语 理财规划书 秋日的私语作文300字 工会工作集体典型事迹材料 大学生军训总结报告 一盘嫩菠菜 初中初一作文750字:西瓜派之寻找历险(11) 巴黎文化产业的现状、特征与发展空间 店面租赁合同(精简版) 小草的心 环境污染的句子 观海老龙头作文800字 感谢黑暗作文550字 小笨蛋和小聪明 公司营销部工作总结 “严纪律,抓学风”全面创新服务型团总支工作方案 黑板报资料—新学期寄语 关于爱情的句子 那些以前说着永不分离的人,早已经散落在天涯了 “只要去试,成功就有希望” 《雷锋的故事》读后感 小学六年级作文600字:观阅兵式有感 风雨兰作文500字 教师自我鉴定总结 2013新年祝福祝福语大全 我流泪 高考满分作文1000字_拒绝平庸 风沙渡 三八节作文1200字 人音版六年级上册 大中国教案 “严以律己”研讨会发言稿:严于律己 敬畏为先 我们有口风琴了 秋天的景色 《短行随想》 中国,我想对你说 我爱我的野蛮同学作文500字 [图文]感悟母爱 张荃蕙 没有体会过贫穷,怎么能拿得起富有 被冤枉的一件事(张玟璐) 高兴的春节 意味无穷的绝妙 高二政治教学反思 《雨》 今晚,我睡不着作文500字

Copyright © 2016 phpStudy |