前段时间在公司做webservice接口,需要先把数据转存到一个中间库里,然后再转移到正式库上,转移的时候需要用到存储过程进行转移数据。本人工作年限有限,经验
不足,不过照着oracle的语法鼓捣出几个存储过程,当时心中还颇为自得。实践出真知,一到执行的时候才发现,数据量小于一万运行速度还可以,数据量一上来就慢的要死,
一分钟才转移几百条数据,几十万条数据要好几个小时。。。。。这是不能容忍的,开始了存储过程的优化之旅。
优化第一步:我发现从中间库中有许多重复数据(也许是转存重复了),刚开始对重复数据也要进行处理,怎么把重复数据去掉呢,刚开始我想起了distinct,不过distinct
对一个字段可以去重复,如果是多个字段去重复就不那么好使了,我用的是rowid,具体语法是 select 查询列(1,2,。。n) from 表名 where rowid =(select * from 表
名+ where +唯一主键 ),因为我们使用的是业务主,所以是多个条件。这样的话我处理的都是不重复的数据,能减少百分之五的工作量。
优化第二步:在去除重复之后,速度还是不尽人意。想想还有什么可以优化的,这次我选择了索引。其实存储过程中最耗时的是查询和插入两种操作。刚开始我没有加索
引,查询需要耗费很多资源,加了索引速度快了百分之十以上。(当然,加上索引数据查询速度加快,但是插入操作的时间变长了,我们是在存储过程中查询的表加上索引,执
行完之后让索引失效)。
优化第三步:加上索引之后感觉速度快了不少,但是大数据量操作还是不可以接受。这次我选择了分步执行,将存储过程中的复杂操作分成多个简单的存储过程,这样速度
提上来了,执行速度比较快了,。
优化第四步:现在的存储已经能够满足现有的需要,所以只能以后在做优化了(数据在生产机上,不能随便操作),但是优化到这我想下面的优化无非以下三个思路:第一
个是分步执行,简化操作。第二个,将数据分部分执行具体方法是,(方案一)将一部处理过的数据转移到中间表里,然后用insert into select。。。操作批量将数据转移,这
种速度无疑是比较快的(因为原来的是查询和插入两个操作,如果是批量的就是每条数据都要执行一次,采用这种方法将重复循环变成大批量操作速度大大提高)。(方案二)
将数据分页,这种我没有实践过,所以不知道效果如何,但是网上评价很高,他和上面的思路是一样的,都是对数据执行大批量处理。
到此我的优化就告一段落,
下面这篇文章是我从网上找的存储过程优化笔记,希望对大家有帮助,呵呵。