哪位高手帮忙优化一下存储过程

[bingzhea]

下面是我写的一个存储过程，第一次写存储过程，运行速度很慢，哪位高手帮忙给提点意见，这个过程该怎么优化，或者哪里需要优化.

谢谢各位了！


CREATE OR REPLACE PROCEDURE test_sjjc(
lctable IN VARCHAR,
lctabzj IN NUMBER ,
lczj IN YWSLLCSLB.ZJ%TYPE
)
IS
str_zj VARCHAR2 (200); --sql语句的主键值变量
str_up VARCHAR2 (2000); --sql语句的临时变量
str_p VARCHAR2 (2000); --sql语句的临时变量
wj VARCHAR2 (200); --sql语句中外键的临时变量
zjm VARCHAR2 (200); --sql语句中的主键名
checkid NUMBER := 0; --判断第一个循环是否执行

TYPE sel IS REF CURSOR; --声明一个游标
sel_zzj sel; --定义一个游标
BEGIN
FOR i_table IN ((SELECT t1.table_name tname
FROM user_constraints t1,user_cons_columns t2
WHERE t1.r_constraint_name = t2.constraint_name
AND t2.table_name = lctable
INTERSECT
SELECT DISTINCT(BYWM) tablename
FROM SJJC_ZDJCTABLE,
(SELECT ywlcdm
FROM YWSSLB
WHERE ZJ = lczj) ywlcdm_t
WHERE LCDM = ywlcdm_t.ywlcdm)
MINUS
SELECT DISTINCT bywm
FROM sjjc_output s
WHERE s.lcslzj = lczj)
LOOP
checkid := checkid + 1;

--检索出表的主键
SELECT col.column_name cname
INTO zjm
FROM user_constraints con,
user_cons_columns col
WHERE con.constraint_name = col.constraint_name
AND con.constraint_type = 'P'
AND con.table_name = i_table.tname;

--检索出表的外键名
SELECT r.column_name
INTO wj
FROM user_cons_columns col,
(SELECT coln.table_name, coln.column_name, conn.r_constraint_name
FROM user_constraints conn, user_cons_columns coln
WHERE conn.constraint_name = coln.constraint_name
AND coln.table_name = i_table.tname) r
WHERE r.r_constraint_name = col.constraint_name
AND col.table_name = lctable;

str_p :=
' SELECT ' || zjm ||
' FROM ' || i_table.tname ||
' WHERE ' || wj || ' = '|| lctabzj;
str_up :=
str_p ||
' UNION ALL ' ||
' SELECT DISTINCT(0) ' || zjm ||
' FROM dual ' ||
' WHERE NOT EXISTS(' || str_p || ')';
OPEN sel_zzj FOR str_up;
LOOP
--得到和流程相关的表的主键值
FETCH sel_zzj INTO str_zj;
EXIT WHEN sel_zzj%NOTFOUND;
--执行检查模块
TEST_SJJC_GYTDSYQDJ_CRDJ(i_table.tname, str_zj, lczj);
IF str_zj != 0 AND str_zj IS NOT NULL
THEN
test_sjjc(i_table.tname, str_zj,lczj);
END IF;
END LOOP;
CLOSE sel_zzj;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END test_sjjc

[bingzhea]

[Quote=引用 1 楼 zhangwonderful 的回复:]
intersect，对两个结果集进行交集操作，已经不包括重复行，所以不需要加distinct
Minus，对两个结果集进行差操作，已经不包括重复行，所以不需要加distinct
建议先将数据放到临时表中，然后从临时表中提取进行循环处理，性能会高很多的。
[/Quote]
谢谢，已修正这个问题。

[bingzhea]

现在还有一个问题就是服务器比我的电脑性能要好，可是我在本地执行要1秒多，在服务器上要4，5秒，我本地的库是从服务器上取下来的，没有比服务器更多的优化设置，可是为什么却比服务器快这么多呢。
很奇怪啊。

[tangren]

服务器性能好，但关键要看有没有其他业务，有多少并发用户
本机配置虽然差，但却只有一个用户，大部分的资源用于数据库
本机比服务器性能高是可能的；

另外，你最好将你的思路和要完成的功能描述一下，更有利于大家帮助你提点优化的建议

[huangyunzeng2008]

可能你本地的库配置参数和服务器上的不一样吧！

[crazylaa]

感觉循环内部搜索主键和外键的两条查询是否可以合并成一条查询。
还有str_up这个sql，似乎也可以优化，因为后面又是从dual去查not exists的。
还有TEST_SJJC_GYTDSYQDJ_CRDJ(i_table.tname, str_zj, lczj);
IF str_zj != 0 AND str_zj IS NOT NULL
THEN
test_sjjc(i_table.tname, str_zj,lczj);
END IF;
递归调用可能会有问题，比如递归的某一个commit了，外层rollback不回去吧？

[zhangwonderful]

intersect，对两个结果集进行交集操作，已经不包括重复行，所以不需要加distinct
Minus，对两个结果集进行差操作，已经不包括重复行，所以不需要加distinct
建议先将数据放到临时表中，然后从临时表中提取进行循环处理，性能会高很多的。

[碧水幽幽泉]

LOOP 里面再嵌套LOOP , 性能肯定差了多少啊！
可以采用分析函数来解决内层循环的问题，那样性能会高很多！

[bingzhea]

[Quote=引用 8 楼 crazylaa 的回复:]
感觉循环内部搜索主键和外键的两条查询是否可以合并成一条查询。
还有str_up这个sql，似乎也可以优化，因为后面又是从dual去查not exists的。
还有TEST_SJJC_GYTDSYQDJ_CRDJ(i_table.tname, str_zj, lczj);
            IF str_zj != 0 AND str_zj IS NOT NULL
            THEN
                test_sjjc(i_table.tname, str_zj,lczj);
            END IF;
递归调用可能会有问题，比如递归的某一个commit了，外层rollback不回去吧？
[/Quote]

主外键的查询确实可以放在一块，但是效率和分开差不多，写一块后要嵌套子查询，比现在的长，可读性就差了；
NOT EXISTS的我再想想，看看如何再优化。
关于递归commit的确实是这样，应该再完善一下。

[bingzhea]

[Quote=引用 6 楼 tangren 的回复:]
服务器性能好，但关键要看有没有其他业务，有多少并发用户
本机配置虽然差，但却只有一个用户，大部分的资源用于数据库
本机比服务器性能高是可能的；

另外，你最好将你的思路和要完成的功能描述一下，更有利于大家帮助你提点优化的建议

[/Quote]

服务器性能这个我也是这么想的，服务器业务肯定比本地多，只是没想到差这么多。

我要完成的功能是给出一个流程表名及主键，在数据库中通过这个表获得和这个流程表相关的所有业务表，并通过TEST_SJJC_GYTDSYQDJ_CRDJ（）过程进行处理，这个TEST_SJJC_GYTDSYQDJ_CRDJ 过程效率还行，我现在列出的只是查找从从表关联主表的内容，还有一个和从主表关联从表的过程，很类似，没有列出来。
user_cons_column和 user_constraints 是系统视图，通过这两个视图获得相关表的关系。

[oraclemch]

关注了！

[bingzhea]

还有没有人再补充一下啊