数据库为oracle,存储过程如下:create or replace procedure proc_contract_jczz(
rep_unit IN VARCHAR2,
rep_date IN VARCHAR2,
rep_sh IN VARCHAR2,
rep_code IN VARCHAR2
)
AS v_consql_row VARCHAR2(2000);
v_seq_row varchar2(2000);
v_consql_col VARCHAR2(2000);
v_seq_col varchar2(2000);
v_sql VARCHAR2(2000);
v_starTime varchar2(40);
v_endTime varchar2(40);
v_i integer;
v_count1 integer;
v_count2 integer;
BEGINv_endTime := rep_date ;
proc_report_time(rep_code,v_starTime,v_endTime);DECLARE
-------------------------------------------------------------------
---------------------------建立行游标 ----------------------------
-------------------------------------------------------------------
CURSOR c_row IS select CONSQL,SEQ from dic_report_format WHERE REPCODE=rep_code
AND SPANTYPE='2' and seq<6 ORDER BY SEQ;
BEGIN
OPEN c_row;
LOOP
FETCH c_row INTO v_consql_row,v_seq_row;
EXIT WHEN c_row%NOTFOUND;
declare
-------------------------------------------------------------------
---------------------------建立列游标 ----------------------------
-------------------------------------------------------------------
CURSOR c_col IS select CONSQL,SEQ from dic_report_format WHERE REPCODE=rep_code
AND SPANTYPE='1' and seq>1 ORDER BY SEQ;
begin
open c_col;
loop
fetch c_col into v_consql_col,v_seq_col;
exit when c_col%notfound; /* 1 */
v_sql:='select count(*) from a01,a17 where a01.id=a17.pid and a17.endflag=''Y'' and to_date(a17007,''yyyy/mm/dd'')>to_date('''||v_starTime||''',''yyyy/mm/dd'') and to_date(a17007,''yyyy/mm/dd'')<=to_date('''||v_endTime||''',''yyyy/mm/dd'') and a01001 like '''||rep_unit||'%'' and '||v_consql_row||' and '||v_consql_col||''; execute immediate v_sql into v_count1; /* 2 */
v_sql:='select count(*) from a54,a17 where a54.pid=a17.pid and a17.endflag=''Y'' and to_date(a17007,''yyyy/mm/dd'')>to_date('''||v_starTime||''',''yyyy/mm/dd'') and to_date(a17007,''yyyy/mm/dd'')<=to_date('''||v_endTime||''',''yyyy/mm/dd'') and a01001 like '''||rep_unit||'%'' and '||v_consql_row||' and '||v_consql_col||''; execute immediate v_sql into v_count2;
v_sql:='UPDATE REP_DATA_DETAIL SET v'||v_seq_col||'='||to_char(v_count1+v_count2);
v_sql:=v_sql||' WHERE REPCODE='''||rep_code||''' AND UNITCODE='''||rep_unit||''' AND REPDATE='''||rep_date||''' AND ISSH='''||rep_sh||''' AND SEQ='''||v_seq_row||'''';
execute immediate v_sql;
END LOOP;
CLOSE c_col;
end;
END LOOP;
CLOSE c_row;
COMMIT; END;
UPDATE REP_DATA_DETAIL SET
V1=V2+V3+V4+V5+V6+V7+V8+V10+V11+V12+V13+V14+V15+V16+V17
WHERE REPCODE=rep_code AND UNITCODE=rep_unit AND REPDATE=rep_date AND ISSH=rep_sh;
commit;UPDATE REP_DATA_DETAIL SET (
V1,V2,V3,V4,V5,V6,V7,V8,V9,V10,V11,V12,V13,V14,V15,V16,V17
) = (
SELECT SUM(V1),SUM(V2),SUM(V3),SUM(V4),SUM(V5),SUM(V6),SUM(V7),SUM(V8),SUM(V9),SUM(V10),SUM(V11),
SUM(V12),SUM(V13),SUM(V14),SUM(V15),SUM(V16),SUM(V17)
FROM REP_DATA_DETAIL
WHERE REPCODE=rep_code AND UNITCODE=rep_unit AND REPDATE=rep_date AND ISSH=rep_sh
AND SEQ<6
)
WHERE REPCODE=rep_code AND UNITCODE=rep_unit AND REPDATE=rep_date AND ISSH=rep_sh
AND SEQ=6;commit;
END proc_contract_jczz;速度慢主要是1和2两个地方;
单独执行1或者2的时间小于0.2秒,其它的操作在屏蔽掉1或者2的时候时间不到0.5秒。c_row 次数为5次,
c_col 次数为16次如下计算:0.2*2*16*5=32秒,再加上更新等操作4秒,不超过36秒;但是慢的有点离谱,85-95秒左右。初学,不知道什么地方可以提高速度或者有偶写的不准确的地方。
偶苦思2个小时后无果,敬请指教!
rep_unit IN VARCHAR2,
rep_date IN VARCHAR2,
rep_sh IN VARCHAR2,
rep_code IN VARCHAR2
)
AS v_consql_row VARCHAR2(2000);
v_seq_row varchar2(2000);
v_consql_col VARCHAR2(2000);
v_seq_col varchar2(2000);
v_sql VARCHAR2(2000);
v_starTime varchar2(40);
v_endTime varchar2(40);
v_i integer;
v_count1 integer;
v_count2 integer;
BEGINv_endTime := rep_date ;
proc_report_time(rep_code,v_starTime,v_endTime);DECLARE
-------------------------------------------------------------------
---------------------------建立行游标 ----------------------------
-------------------------------------------------------------------
CURSOR c_row IS select CONSQL,SEQ from dic_report_format WHERE REPCODE=rep_code
AND SPANTYPE='2' and seq<6 ORDER BY SEQ;
BEGIN
OPEN c_row;
LOOP
FETCH c_row INTO v_consql_row,v_seq_row;
EXIT WHEN c_row%NOTFOUND;
declare
-------------------------------------------------------------------
---------------------------建立列游标 ----------------------------
-------------------------------------------------------------------
CURSOR c_col IS select CONSQL,SEQ from dic_report_format WHERE REPCODE=rep_code
AND SPANTYPE='1' and seq>1 ORDER BY SEQ;
begin
open c_col;
loop
fetch c_col into v_consql_col,v_seq_col;
exit when c_col%notfound; /* 1 */
v_sql:='select count(*) from a01,a17 where a01.id=a17.pid and a17.endflag=''Y'' and to_date(a17007,''yyyy/mm/dd'')>to_date('''||v_starTime||''',''yyyy/mm/dd'') and to_date(a17007,''yyyy/mm/dd'')<=to_date('''||v_endTime||''',''yyyy/mm/dd'') and a01001 like '''||rep_unit||'%'' and '||v_consql_row||' and '||v_consql_col||''; execute immediate v_sql into v_count1; /* 2 */
v_sql:='select count(*) from a54,a17 where a54.pid=a17.pid and a17.endflag=''Y'' and to_date(a17007,''yyyy/mm/dd'')>to_date('''||v_starTime||''',''yyyy/mm/dd'') and to_date(a17007,''yyyy/mm/dd'')<=to_date('''||v_endTime||''',''yyyy/mm/dd'') and a01001 like '''||rep_unit||'%'' and '||v_consql_row||' and '||v_consql_col||''; execute immediate v_sql into v_count2;
v_sql:='UPDATE REP_DATA_DETAIL SET v'||v_seq_col||'='||to_char(v_count1+v_count2);
v_sql:=v_sql||' WHERE REPCODE='''||rep_code||''' AND UNITCODE='''||rep_unit||''' AND REPDATE='''||rep_date||''' AND ISSH='''||rep_sh||''' AND SEQ='''||v_seq_row||'''';
execute immediate v_sql;
END LOOP;
CLOSE c_col;
end;
END LOOP;
CLOSE c_row;
COMMIT; END;
UPDATE REP_DATA_DETAIL SET
V1=V2+V3+V4+V5+V6+V7+V8+V10+V11+V12+V13+V14+V15+V16+V17
WHERE REPCODE=rep_code AND UNITCODE=rep_unit AND REPDATE=rep_date AND ISSH=rep_sh;
commit;UPDATE REP_DATA_DETAIL SET (
V1,V2,V3,V4,V5,V6,V7,V8,V9,V10,V11,V12,V13,V14,V15,V16,V17
) = (
SELECT SUM(V1),SUM(V2),SUM(V3),SUM(V4),SUM(V5),SUM(V6),SUM(V7),SUM(V8),SUM(V9),SUM(V10),SUM(V11),
SUM(V12),SUM(V13),SUM(V14),SUM(V15),SUM(V16),SUM(V17)
FROM REP_DATA_DETAIL
WHERE REPCODE=rep_code AND UNITCODE=rep_unit AND REPDATE=rep_date AND ISSH=rep_sh
AND SEQ<6
)
WHERE REPCODE=rep_code AND UNITCODE=rep_unit AND REPDATE=rep_date AND ISSH=rep_sh
AND SEQ=6;commit;
END proc_contract_jczz;速度慢主要是1和2两个地方;
单独执行1或者2的时间小于0.2秒,其它的操作在屏蔽掉1或者2的时候时间不到0.5秒。c_row 次数为5次,
c_col 次数为16次如下计算:0.2*2*16*5=32秒,再加上更新等操作4秒,不超过36秒;但是慢的有点离谱,85-95秒左右。初学,不知道什么地方可以提高速度或者有偶写的不准确的地方。
偶苦思2个小时后无果,敬请指教!
2 优化sql , 一 先执行选择操作 ,然后再执行联结操作 。
这个没有必要转换4次吧?
只要作一次转换并赋予某个变量,其他地方引用这个变量.
只说——谢谢!to : mixtrue(忽报人间曾伏虎,泪飞顿作倾盆雨。)
表的索引都加了,但是数据量还是很大,还是有影响……to :yaray(雅睿,生活在别处)
恩,有道理。
c_row 次数为5次,
c_col 次数为16次
如下计算:0.2*2*16*5=32秒,再加上更新等操作4秒,不超过36秒;你最好不要这样计算,单独执行和嵌套执行不只是相乘这么简单,其中还有好多因素要考虑
举个例子:
你单独执行1需要占用内存10M,单独执行2需要占用15M,但是嵌套时,2的执行是在内存占用大概在20M以上的条件下执行的(大于大的,小于两个之和),速度肯定会慢
还有就是CPU要不断的进行入栈、出栈,来保存交互时的临时数据。
不过象yaray(雅睿,生活在别处) 大侠说的那样,优化一下还是必须的,能提高一些就提高一些
(纯属个人意见,不知道是否有道理^_^)
谢谢你的分析,最近服务器总是很忙,可能是这个问题。to : luckycat(潘鑫)
你挺幽默的,不过我知道你的意思,游标很费内存吧。
我自己可以尝试(虽然你没在多说几个字),但是还是要说一句“谢谢”!