1、Select语句优化要点
(1) 对于大数据量的求和应避免使用单一的sum命令处理,可采用group by方式与其结合,有时其效率可提高几倍甚至百倍。例如,银行常
要进行帐户的总帐与明细帐一致性核对(总分核 对),数据量大,原采用单一的sum命令与while语句结合来完成,现改用以下group by
方式后效率大相径庭。
/*将定期表中所有数据按机构,储种统计户数,余额置临时表中并建索引*/
select zh[1,9] jg,zh[19,20]cz,count(*)hs,sum(ye)sumye from satdq where bz=″0″
group by zh[1,9],zh [19,20] into temp satdq_sum;
create index satdq_suml on satdq_sum(jg,cz); (帐号zh的前9位为机构编码,第19至20位为储种)
(2) 最具有限制性的条件放在前面,大值在前,小值在后。
如:where col<=1000 and col>=1 效率高 where col>=1 and col<=1000 效率低
(3)避免子查询与相关查询。
如:where zh in (select zh from table where xm matches ″*11*″) 可将其编为declare cursor 的一while循环来处理。
(4)避免会引起磁盘读写的rowid操作。
在where子句中或select语句中,用rowid要产生磁盘读写,是一个物理过程,会影响性能。
如原为:
declare ps2 cursor for select *,rowid into b,id from satmxhz
where zh[1,9]=vvjgbm and bz=″0″ order by zh; open ps2; fetch ps2;
while (sqlca.sqlcode==0){ ……
update satmxhz set
sbrq=b.sbrq, ye=b.ye, lxjs=b.lxjs, wdbs=wdbs+1, dac=dac
where rowid=id;
……
fetch ps2; }
改为:
declare ps2 cursor for select * into b from satmxhz
where zh [1,9]=vvjgbm and bz=″0″
for update of sbrq,ye,lxjs,wdbs,dac; open ps2; fetch ps2;
while (sqlca.sqlcode==0){
……
update satmxhz set
sbrq=b.sbrq, ye =b.ye, lxjs=b.lxjs, wdbs=b.wdbs, dac=dac
where current of ps2; ……
fetch ps2; }
(5)where子句中变量顺序应与索引字键顺序相同。
如:create index putlsz_idx on putlsz(zh ,rq,lsh)
索引字键顺序:首先是帐号zh,其次是日期rq,最后是流水号lsh,
所以where子句变量顺序应是where zh=″11111″and rq=″06/06/1999″and lsh<1000,不应是where lsh<1000 and rq=″06/06/1999″ and zh =″11111″等非索引字键顺序。
(6)用=替代matches的操作。
如:where zh matches ″330678860*″应用where zh[1,9]=″330678860″替代。
(7)通过聚族索引cluster index提高效率。
(8)避免使用order by,group by,该操作需生成临时表而影响效率,可用视图来处理,视图的引入能控制用户的存取,提高效率。
2、insert语句优化要点
(1)采用insert cursor或put替代insert; 如:wr_satmx () begin work;
prepare insert_mx from ″insert into satmx values(?,?,?,?,?,?,?,?,?,?,?,?)″;
declare mx_cur cursor for insert_mx;
open mx_cur;
declare cur_mxh cursor for
select * into bmxh from satmxh for update;
open cur_mxh;
fetch cur_mxh;
while (sqlca.sqlcode==0){ put mx_cur from
bmxh.zh ,bmxh,rq,bmxh,l sh,bmxh,jym, bmx,pzhm,bmxh.bz,bmxh,fse,
bmxh.ye,bmxh.bdlsh,bmxh.bd rq,bmxh.czy,bmxh.dybz; delete from satmxh
where current of cur_mxh; fetch cur_mxh; }
close mx_cur; close cur_mxh; commit work;
以上一段程序是将satmxh表中记录转移到satmx表中,虽然可用 begin work;
insert into satmx select * from satmxh; dele te from satmxh; commit work;
四行程序即可实现,但若表中记录多的话,其运行效率远远不如前者的处理方式,因为insert cursor是先在共享内存缓存中处理,刷新时写入磁盘的,所以上载数据速度最快,但其缺点是必须编程实现。
(2)避免加长锁、长事务操作,这在处理大数据量时其优劣尤为突出,在能保证数据一致性的前提下应将长事务分解为小事务来处理。
如将前面例题数据分不同网点机构进行转移,避免长事务,可大大提高运行效率。
wr_satmx():
database workdb;
declare cur_jgl cursor with hold for select jgbm,jgmc into vvjgbm,vvjgmc from putjgbm order by jgbm open cur_jgl;
fetch cur_jgl;
while(sqlca.sqlcode==0){ begin work;
prepare insert_mx from ″insert into satmx values(?,?,?,?,?,?,?,?,?,?,?,?)″; declare mx_cur cursor for insert_mx open mx_cur
declare cur_mxh cursor for
select * into bmxh from satmxh
where zh [1,9]=vvjgbm for update; open cur_mxh; fetch cur_mxh;
while (sqlca.sqlcode==0){ put mx_cur from
bmxh.zh,bmxh.rq,bmxh.lsh,bmxh,jym, bmx.pzhm,bmxh.bz,bmxh.fse, bmxh.ye,bmxh.bdlsh,bmxh.bd rq,bmxh.czy,bmxh.dybz; delete from satmxh
where current of cur_mxh; fetch cur_mxh; }
close mx_cur; close cur_mxh; commit work; fetch cor_jgl; }
close cur_jgl; close database;
(3)宿主变量应在执行insert操作前转换为表结构描述的数据类型,避免insert语句操作时不同数据类型自动转换而影响其效率。
(4)对表的insert操作很频繁时,可以将index fill factor降低一些,采用row lock 代替page lock。
3、update语句优化要点
(1)用子串代替matches,避免使用不从第一个开始的子串。
如where a matches ″ab*″采用where a [1,2]=″ab″代替;避免使用如b[5,6]的子串。
(2)避免加长锁修改,避免长事务处理,例子参见insert的语句优化(2)方式。
4.delete语句优化要点
(1)用drop table,create table和create index代替delete from table,能快速清理并释放表空间。
(2)避免长事务处理,例子参见insert的语句优化(2)方式。
(3)使用关联(父子)删除cascading delete。
(4)编写程序使用delete cursor删,而不采用delete from table where…的方式。例子参见insert的语句优化(1)方式。