也谈内存的消耗

[ruin]

我最近做了一个查询的小程序,仅用了两重的循环,最内层的语句大概执行了150次,这是查询的结果就变得比较慢,我也不知道这是怎么回事,请高手指教:
现在我将我的源码贴出来::
for i=1 to 31 step 1
ls_personcode=dw_bb_attendstat.getitemstring(i,"eperson_personcode")

select count(PersonCode)
into :li_comelate
from eAttendance
where personcode=:ls_personcode and checkdate>=:ld_date1 and checkdate<=:ld_date2 and comelate='是'
;

select count(PersonCode)
into :li_leaveearly
from eAttendance
where personcode=:ls_personcode and checkdate>=:ld_date1 and checkdate<=:ld_date2 and leaveearly='是'
;
do while(ld_date<=ld_date2)
select count(PersonCode)
into :n
from epersonexception
where personcode=:ls_personcode and excepstartdate<=:ld_date and excependdate>=:ld_date and exceptype='病假'
;
if n>0 then
li_sicktime++
end if

select count(PersonCode)
into :n
from epersonexception
where personcode=:ls_personcode and excepstartdate<=:ld_date and excependdate>=:ld_date and exceptype='薪假'
;
if n>0 then
li_salarytime++
end if

select count(PersonCode)
into :n
from epersonexception
where personcode=:ls_personcode and excepstartdate<=:ld_date and excependdate>=:ld_date and exceptype='旷工'
;
if n>0 then
li_offtime++
end if

select count(PersonCode)
into :n
from epersonexception
where personcode=:ls_personcode and excepstartdate<=:ld_date and excependdate>=:ld_date and exceptype='事假'
;
if n>0 then
li_shitime++
end if

select count(PersonCode)
into :n
from epersonexception
where personcode=:ls_personcode and excepstartdate<=:ld_date and excependdate>=:ld_date and exceptype<>'事假' and exceptype<>'旷工' and exceptype<>'薪假' and exceptype<>'病假'
;
if n>0 then
li_othertime++
end if

ld_date=relativedate(ld_date,1)
loop

dw_bb_attendstat.setitem(dw_bb_attendstat.getrow(),"emonthreport_comelate",li_comelate)
dw_bb_attendstat.setitem(dw_bb_attendstat.getrow(),"emonthreport_leaveearly",li_leaveearly)
dw_bb_attendstat.setitem(dw_bb_attendstat.getrow(),"emonthreport_sicktimes",li_sicktime)
dw_bb_attendstat.setitem(dw_bb_attendstat.getrow(),"emonthreport_salarytimes",li_salarytime)
dw_bb_attendstat.setitem(dw_bb_attendstat.getrow(),"emonthreport_offtimes",li_offtime)
dw_bb_attendstat.setitem(dw_bb_attendstat.getrow(),"emonthreport_shitimes",li_shitime)
dw_bb_attendstat.setitem(dw_bb_attendstat.getrow(),"emonthreport_other",li_othertime)

li_comelate=0
li_leaveearly=0
li_sicktime=0
li_salarytime=0
li_offtime=0
li_shitime=0
li_othertime=0

dw_bb_attendstat.scrollnextrow()
ld_date=ld_date1

INSERT INTO emonthreport
( personcode,
dateofmonth,
comelate,
leaveearly,
sicktime,
salarytime,
shitime,
other,
offtimes )
)
VALUES ( :ls_personcode,
:li_month1,
:li_comelate,
:li_leaveearly,
:li_sicktime,
:li_salarytime,
:li_shitime,
:li_othertime,
:li_offtime ) ;

next

[bency]

如果你的数据量比较大时，用select count() ... 速度是会比较慢
建议程序在某一个时段自动执行

[iamxia]

...

[tiantianpb]

这种问题，不如写个存储过程后，在加入job，让它在特定的时间段执行吧。，如果改写成存储过程应该比你现在的这些代码要快

[tiantianpb]

这种问题，不如写个存储过程后，在加入job，让它在特定的时间段执行吧。，如果改写成存储过程应该比你现在的这些代码要快

[ruin]

什么意思??

不明白?

[chinalht]

一个在本例中影响不大的问题的建议
dw_bb_attendstat.setitem(dw_bb_attendstat.getrow(),"emonthreport_comelate",li_comelate)
改为：
long ll_row

ll_row = dw_bb_attendstat.getrow()
dw_bb_attendstat.emonthreport_comelate[ll_row] = li_comelate