在fldmk.jszl、flsfcyb.rq、fldmk.bm=flsfcyb.bm上建索引
select count(*) into :MCount from fldmk,flsfcyb
where fldmk.jszl=:fgr and flsfcyb.rq=:PreMDate and fldmk.bm=flsfcyb.bm;
INSERT INTO flsfcyb(bm, rq, byjc, hj, pjj, bygj, syjc, kfbmdm)
SELECT fldmk.bm, :EnDate, 0, 0, 0, 0, 0, :ckdm FROM fldmk,flsfcyb
WHERE kfbmdm=:ckdm and jszl=:fgr and flsfcyb.rq=:EnDate)
and (fldmk.bm = flsfcyb.bm(+) and (flsfcyb.bm is null);
下列语句如何优化,我在执行时竟要等待10分钟左右,太慢了
1、select count(*) into :MCount from fldmk,flsfcyb where
fldmk.bm=flsfcyb.bm and fldmk.jszl=:fgr and flsfcyb.rq=:PreMDate;
2、INSERT INTO flsfcyb(bm, rq, byjc, hj, pjj, bygj, syjc, kfbmdm)
SELECT fldmk.bm, :EnDate, 0, 0, 0, 0, isnull(fldmk.cskc,0), :ckdm FROM fldmk,flsfcyb WHERE fldmk.kfbmdm=:ckdm and fldmk.bm=flsfcyb.bm and fldmk.jszl=:fgr;
3、delete flsfcyb from fldmk where fldmk.bm=flsfcyb.bm and fldmk.jszl=:fgr and flsfcyb.kfbmdm=:ckdm and flsfcyb.rq=:EnDate;
4、insert into flsfcyb(bm,rq,syjc,bygj,hj,byjc,pjj,kfbmdm)
select flsfcyb.bm,:EnDate,isnull(flsfcyb.byjc,0),0,0,0,0,:ckdm from fldmk,flsfcyb where fldmk.bm=flsfcyb.bm and fldmk.jszl=:fgr and flsfcyb.kfbmdm=:ckdm and flsfcyb.rq=:PreMDate;
5、INSERT INTO flsfcyb(bm, rq, byjc, hj, pjj, bygj, syjc, kfbmdm)
SELECT fldmk.bm, :EnDate, 0, 0, 0, 0, 0, :ckdm FROM fldmk
WHERE kfbmdm=:ckdm and jszl=:fgr and bm not in(select bm from flsfcyb where rq=:EnDate) ;
6、update flsfcyb set
bygj = isnull((select sum(flrkd.sl) from flrkd
where bm=flsfcyb.bm and flrkd.jszl=:fgr and zdrq>=:BeginDate and zdrq<=:EnDate),0) WHERE flsfcyb.kfbmdm=:ckdm and rq=:EnDate;
7、update flsfcyb set flsfcyb.byjc=round(isnull(flsfcyb.syjc,0) + isnull(flsfcyb.bygj,0) - isnull(flsfcyb.hj,0),5)
from fldmk WHERE flsfcyb.kfbmdm=:ckdm and fldmk.jszl=:fgr and flsfcyb.rq=:EnDate;