放分中，关于频繁访问，速度很慢的问题。

[jdsnhan]

关于频繁访问，速度很慢的问题


--基础表
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;

[jdsnhan]

看不出楼上方法的意义。

[vc555]

很慢的瓶颈是在哪呢？在等待什么？

用户定义的公式是可以预知的有限个吗？

[oracledbalgtu]

如果没有下面的index，创建一个试试。
CREATE INDEX i_e_result_test ON e_result(tempid,trow,tcol);

[Quote=引用楼主 jdsnhan 的帖子:]
关于频繁访问，速度很慢的问题


--基础表
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 …
[/Quote]

[oraclelogan]

[Quote=引用楼主 jdsnhan 的帖子:]
关于频繁访问，速度很慢的问题


--基础表
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_resu…
[/Quote]

sql貌似都很优化，不知道业务逻辑能不修改下？

[fosjos]

[Quote=引用 4 楼 jdsnhan 的回复:]
看不出楼上方法的意义。
[/Quote]
不需要在存储过程中调用一大堆的instr和substr，用来解释YS

[jdsnhan]

能优化一点的就是使用变量绑定，可惜，收效甚微。
业务上很难优化了，苦闷中。。。。。。

[fosjos]

真巧，最近也在做公式计算

我是这样实现的，比如'YS(105,E,12)+YS(103,C,9)'，先replace所有E和C，定义function: YS
然后拼成select YS(105,x,12)+YS(103,x,9) from dual

虽然效率上看不出能否优化，代码肯定简洁，希望对你有所提示

[smll8402]

可以不用Loop么?直接用SQL???

[wzjpsq]

不懂,学习

[oraclelogan]

再次接分中。

[jdsnhan]

索引存在。
不用loop，如何能解析出'YS(105,E,12)+YS(103,C,9)' 这样的公式呢

[hjx000]

建议建立函数索引，只要8i以上的enterprise版本都可以建立函数索引。
同时在过程中增加每一行执行的时间，精确到毫秒，找出执行速度瓶颈，有针对性地进行优化

[fosjos]

create function YS(t_tempid number, t_row number, t_col number)
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;

[doer_ljy]

楼主第一步并不是想使用oracle来做计算，
而是通过解析YS(PID,Row,Col)来到result表里查出对应的值。
第二步才是计算各个值。
如果是这样的话，楼主实际要动态构建一个select部分带子查询的动态查询语句。
这个效率不可能太高，再加上公式解析的时间就更不用说了。
12楼的建议可能会提高一点效率，
首先省去了解析的时间，
其次由于在YS函数中使用SQL是绑定的，效率上也会好一点。
总体感觉计算量太大，想不到太好的办法。
倒是有个疑问，为啥基础表里也带行列呢？
基础表里的模板公式中会不会调用基础表中的其他模板呢？
比如：
101,'YS(102,1,A)'=〉102,'YS(105,2,B)'

[AFIC]

用啥oracle存储过程阿，
数据窗，计算列，全局函数肯定比这个快

[多壮志]

解析总是需要消耗调cpu资源,应该是没有疑问的.
所以问题应该在于提高解析的算法,或者是不是你这些公式是固定的值,如果是,那么做基于函数的索引应该是必要的.
这是目前了解的情况.

[BrainStormLi]

mark!~ 好好向高手學習～

[xp_79]

使用内存表吧？

[jdsnhan]

to fosjos
谢谢你提供YS() 这个思路，我再对比一下。

to hjx000
瓶颈在形成sql，再次解析。

to doer_ljy
基础表里的模板公式中会不会调用基础表中的其他模板呢？

会的

to AFIC
交给db处理，肯定比前台处理快

[doer_ljy]

结帖了？
很想知道最后解决方法地说。