关于频繁访问,速度很慢的问题
--基础表
create table e_base (tid number,--主键
trow number,--行
tcol varchar2(2),--列
tempid number,--模板id
tformula varchar2(1000) --公式
);insert into e_base values(1,9,'A',101,'YS(103,A,9)');
insert into e_base values(2,8,'B',101,'YS(101,A,9)');
insert into e_base values(3,4,'D',102,'YS(105,E,12)+YS(103,C,9)');--结果表create table e_result(
pid number,--主键
trow number,--行
tcol varchar2(2),--列
tempid number,--模板id
evalue number --值
);现状:
基础表中公式字段tformula存储的是用户定义的公式,需要到结果表中根据模板id(tempid)对对应解析。select 解析函数(tformula),tempid from e_base where tempid=101;问题:某一模板下公式少的时候,解析速度还可以,如果公式多了,如 4000条记录中,1200条存在公式,解析速度很慢,而且,e_result是最终结果表,数据量也很大。请大家帮忙,寻找个新思路。附解析函数的思路:根据固定标识符:逗号 提出,然后拼出sql Loop
v_bindex := Instr(s_formula, 'YS(');
v_eindex := instr(s_formula, ')', v_bindex);
Exit When(Nvl(v_bindex, 0) = 0);
s_temp := Trim(Substr(s_formula,
v_bindex + 3,
v_eindex - v_bindex - 3));
t_tempid := substr(s_temp,1,instr(s_temp,',')-1);
t_col := substr(s_temp,instr(s_temp,',')+1,instr(s_temp,',',1,2)-instr(s_temp,',')-1);
t_row := substr(s_temp,instr(s_temp,',',1,2)+1,instr(s_temp,',',1,3)-instr(s_temp,',',1,2)-1); s_sql := '(select nvl(evalue,0) from e_result where tempid=' || t_tempid ||' and trow='||t_row||
' and tcol='''||t_col||''')'; s_final := replace(s_final,'YS('||s_temp||')',s_sql);
s_formula := Substr(s_formula, v_eindex + 1);
End Loop;
s_final := 'select '||s_final||' from dual'; execute immediate s_final into s_final; Return s_final;
--基础表
create table e_base (tid number,--主键
trow number,--行
tcol varchar2(2),--列
tempid number,--模板id
tformula varchar2(1000) --公式
);insert into e_base values(1,9,'A',101,'YS(103,A,9)');
insert into e_base values(2,8,'B',101,'YS(101,A,9)');
insert into e_base values(3,4,'D',102,'YS(105,E,12)+YS(103,C,9)');--结果表create table e_result(
pid number,--主键
trow number,--行
tcol varchar2(2),--列
tempid number,--模板id
evalue number --值
);现状:
基础表中公式字段tformula存储的是用户定义的公式,需要到结果表中根据模板id(tempid)对对应解析。select 解析函数(tformula),tempid from e_base where tempid=101;问题:某一模板下公式少的时候,解析速度还可以,如果公式多了,如 4000条记录中,1200条存在公式,解析速度很慢,而且,e_result是最终结果表,数据量也很大。请大家帮忙,寻找个新思路。附解析函数的思路:根据固定标识符:逗号 提出,然后拼出sql Loop
v_bindex := Instr(s_formula, 'YS(');
v_eindex := instr(s_formula, ')', v_bindex);
Exit When(Nvl(v_bindex, 0) = 0);
s_temp := Trim(Substr(s_formula,
v_bindex + 3,
v_eindex - v_bindex - 3));
t_tempid := substr(s_temp,1,instr(s_temp,',')-1);
t_col := substr(s_temp,instr(s_temp,',')+1,instr(s_temp,',',1,2)-instr(s_temp,',')-1);
t_row := substr(s_temp,instr(s_temp,',',1,2)+1,instr(s_temp,',',1,3)-instr(s_temp,',',1,2)-1); s_sql := '(select nvl(evalue,0) from e_result where tempid=' || t_tempid ||' and trow='||t_row||
' and tcol='''||t_col||''')'; s_final := replace(s_final,'YS('||s_temp||')',s_sql);
s_formula := Substr(s_formula, v_eindex + 1);
End Loop;
s_final := 'select '||s_final||' from dual'; execute immediate s_final into s_final; Return s_final;
然后拼成select YS(105,x,12)+YS(103,x,9) from dual虽然效率上看不出能否优化,代码肯定简洁,希望对你有所提示
如果没有下面的index,创建一个试试。
CREATE INDEX i_e_result_test ON e_result(tempid,trow,tcol);
sql貌似都很优化,不知道业务逻辑能不修改下?
不用loop,如何能解析出'YS(105,E,12)+YS(103,C,9)' 这样的公式呢
as
result number;
begin
select nvl(evalue,0) into result from e_result
where tempid=t_tempid and trow=t_row and tcol=t_col;
return result;
end;
create function 解析函数(tformula varchar2, A_value number, B_value number, ......)
as
result number;
s_sql varchar2(1000);
begin
s_sql:=tformula;
s_sql:=replace(s_sql,'A',A_value); --关键是这里是否可行,我的那个公式计算参数比较简单^_^
s_sql:='select ' || s_sql || ' from dual';
execute immediate s_sql into result;
return result;
end;
同时在过程中增加每一行执行的时间,精确到毫秒,找出执行速度瓶颈,有针对性地进行优化
所以问题应该在于提高解析的算法,或者是不是你这些公式是固定的值,如果是,那么做基于函数的索引应该是必要的.
这是目前了解的情况.
而是通过解析YS(PID,Row,Col)来到result表里查出对应的值。
第二步才是计算各个值。
如果是这样的话,楼主实际要动态构建一个select部分带子查询的动态查询语句。
这个效率不可能太高,再加上公式解析的时间就更不用说了。
12楼的建议可能会提高一点效率,
首先省去了解析的时间,
其次由于在YS函数中使用SQL是绑定的,效率上也会好一点。
总体感觉计算量太大,想不到太好的办法。
倒是有个疑问,为啥基础表里也带行列呢?
基础表里的模板公式中会不会调用基础表中的其他模板呢?
比如:
101,'YS(102,1,A)'=〉102,'YS(105,2,B)'
数据窗,计算列,全局函数肯定比这个快
谢谢你提供YS() 这个思路,我再对比一下。to hjx000
瓶颈在形成sql,再次解析。to doer_ljy
基础表里的模板公式中会不会调用基础表中的其他模板呢?会的 to AFIC
交给db处理,肯定比前台处理快