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)方式。

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)方式。