- 论坛徽章:
- 0
|
能否优化一下这个存储过程?
执行以下存储过程的时候特别的慢,看看从优化sql语句的角度能否对以下存储过程作出一些优化呢?(注意这是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\
;| |
|