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位为储种)
- 1.
- 2.
- 3.
- 4.
(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, wdbswdbs=wdbs＋1, dacdac=dac where rowid=id;
……
fetch ps2;
}
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
改为：
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, dacdac=dac where current of ps2;
……
fetch ps2;
}
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
(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;
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
以上一段程序是将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;
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
(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)方式。
【本文为51CTO专栏作者“王森丰”的原创稿件，转载请注明出处】
