有高手幫忙優(yōu)化一下這個存儲過程么？

redred5

能否優(yōu)化一下這個存儲過程？
執(zhí)行以下存儲過程的時候特別的慢，看看從優(yōu)化sql語句的角度能否對以下存儲過程作出一些優(yōu)化呢？（注意這是informix的存儲過程）
CREATE PROCEDURE "informix".p_high_serv( \
oper_id INTEGER ) \
RETURNING INTEGER,VARCHAR(255) ;\
--------------------------------------------------------------------------- \
{ \
\
DELETE FROM PMT_OBJECTS WHERE PMT_ID=210 AND|
PMT_SEQ_NBR=1; \
INSERT INTO PMT_OBJECTS VALUES(210,1,CURRENT,CURRENT,'','p_high_serv'); \
UPDATE PMT_OBJECTS SET START_DATE=CURRENT WHERE PMT_ID=210 AND PMT_SEQ_NBR=1; \
EXECUTE PROCEDURE p_high_serv(1|
); \
-- \
UPDATE PMT_OBJECTS SET END_DATE=CURRENT WHERE PMT_ID=210 AND PMT_SEQ_NBR=1; \
UPDATE PMT_OBJECTS SET SP_TIME=F_SPAN_TIME(END_DATE,START_DATE) WHERE PMT_ID=210 AND PMT_SEQ_NBR=1; \
SEL|
ECT * FROM PMT_OBJECTS WHERE PMT_ID=210 ORDER BY PMT_SEQ_NBR ; \
} \
base_acct_item,base_serv,dim_exchange,dim_area,dim_latn,tmp_acct_item_s,tmp_key_month2\
DEFI|
NE ld_key_month DATETIME YEAR TO MONTH;\
\
DEFINE ON_FLAG INTEGER ; \
DEFINE OUT_REASON VARCHAR(255) ; \
DEFINE LI_ERR_NUM INTEGER ; \
DEFINE LI_ISAM_ERR INTEGER ; \
DEFINE LS_ERR_INFO|
VARCHAR(255) ; \
\
ON EXCEPTION SET LI_ERR_NUM,LI_ISAM_ERR,LS_ERR_INFO \
LET ON_FLAG = -1 ; \
LET OUT_REASON = 'p_high_serv ErrMsg: ,'\|\|LI_ERR_NUM\|\|LS_ERR_INFO ; \
RETURN ON_FLAG,OUT_REASON; \
END EXCEPTION\
\
SELECT DISTINCT key_month\
FROM base_acct_item\
INTO TEMP tmp_key_month2;\
\
FOREACH SELECT key_month\
INTO ld_key_month\
FROM tmp_key_month2\
\
? \
SELECT a.serv_seq_nbr,\
a.serv_id,\
e.latn_id,\
d.area_id,\
c.exchange_id,\
SUM(charge) charge,\
SUM(due_charge) due_charge,\
SUM(call_durati|
on) call_duration,\
SUM(call_times) call_times,\
SUM(meter_times) meter_times \
FROM base_acct_item a,\
base_serv b,\
dim_exchange c,\
dim_area d,\
dim_latn e \
WH|
ERE a.key_month=ld_key_month\
AND a.call_times>0\
AND b.serv_seq_nbr=a.serv_seq_nbr\
AND b.serv_id=a.serv_id\
and b.exchange_id=c.exchange_id\
and c.area_id=d.area_id\
and d.latn_id=e.latn_id\
GROUP|
BY a.serv_seq_nbr,a.serv_id,e.latn_id,d.area_id,c.exchange_id\
INTO TEMP tmp_acct_item_s;\
\
CREATE INDEX idx_tmp_ais ON tmp_acct_item_s(serv_seq_nbr,serv_id); \
\
CALL p_fct_hcserv_ticket(oper_id,ld_key_month) RETUR|
NING ON_FLAG,OUT_REASON;\
\
\

DROP TABLE tmp_acct_item_s;\
\

END FOREACH;\
\

DROP TABLE tmp_key_month2; \
\
\
LET ON_FLAG=0;\
LET OUT_REASON='Success';\
\
RETURN ON_FLAG,OUT_REASON ; \
\
END PROCEDURE\
;|

wenlq

有點亂，重排一下吧

redred5

ok!

wenlq

把里面的那個
select ... FROM base_acct_item a,\
base_serv b,\
dim_exchange c,\
dim_area d,\
dim_latn e \
語句拿到dbaccess 執(zhí)行
加上set explain on ;
看看查詢規(guī)劃。
再看看 p_fct_hcserv_ticket存儲過程的效率。
把所有into temp 語句加上with no log ;

lmtok

這個存儲過程有點復(fù)雜，最好能留trace信息，判斷究竟是在那里的語句比較慢