SQL语句直接执行和在存储过程中速度的问题......同一个SQL语句,放在存储过程中执行比直接执行差了100倍以
SQL语句直接执行和在存储过程中速度的问题......
同一个SQL语句,放在存储过程中执行比直接执行差了100倍以上的速度,很不解啊...这个有什么具体的方法来优化么?
数据库版本是9i......貌似在10g上就没这么大差别..但是由于2个库的数据量是不一样的,所以没什么参考价值
SQL语句:
- SQL code
SQL_INSERT_RL_INFO_AREA CONSTANT varchar2(1000) := 'insert into temp_addr_new ( ROW_NUMBER, GEOGRAPHY_ID, COMMUNITY_ID, DESCRIPTION, NAME, COMMUNITY_NAME, AREA_ID, BUSINESS_TYPE_ID, ' || 'USER_TYPE_ID, MANAGED_CODE ) ' || 'select * from (select rownum r_id, rl.geography_loc_id , c.community_id , rl.description , rl.name rname, c.name cname, rl.area_id , rl.business_type_id , ' || 'rl.user_type_id , rl.manage_code ' || 'from regional_loc rl,regional_loc_2_community rl2c,community c ' || 'where rl.geography_loc_id = rl2c.geography_loc_id and rl2c.community_id = c.community_id and rl.display_type_cd = 1 ' || 'and c.status = :1 ' || 'and rl.description like :2 and c.name like :3 ' || 'and rl.regional_simple_spell like :4 and c.community_simple_spell like :5 ' || 'and rl.user_type_id in ( :6 ,3) ' || 'and c.rsc_spec_id <> :7 ' || 'and rl.area_id = :8 and rownum <= :9 ) where r_id >= :10 '; SQL_SELECT_RL_COUNT_AREA CONSTANT varchar2(1000) := 'select count(1) from regional_loc rl,regional_loc_2_community rl2c,community c ' || 'where rl.geography_loc_id = rl2c.geography_loc_id and rl2c.community_id = c.community_id and rl.display_type_cd = 1 ' || 'and c.status = :1 ' || 'and rl.description like :2 and c.name like :3 ' || 'and rl.regional_simple_spell like :4 and c.community_simple_spell like :5 ' || 'and rl.user_type_id in ( :6 ,3) ' || 'and c.rsc_spec_id <> :7 ' || 'and rl.area_id = :8 ';
执行方式(匿名块和直接执行都是一样):
- SQL code
DECLARE IN_COMMUNITY_NAME VARCHAR2(200); IN_STANDARD_ADDR_NAME VARCHAR2(200); IN_PROD_SPEC_ID NUMBER; IN_BUSINESS_TYPE VARCHAR2(200); IN_USER_TYPE NUMBER; IN_AREA_ID NUMBER; IN_CONDITION_TYPE VARCHAR2(200); IN_BEGIN_ROW NUMBER; IN_END_ROW NUMBER; OUT_ATTRS PG_RMS_FOR_CRM_NEW.c_list; OUT_ROW_COUNT NUMBER; OUT_RET_CODE VARCHAR2(200); OUT_RET_INFO VARCHAR2(200); v_gpon_not_rsc_spec number; v_start number; v_rl_chinese varchar2(400); v_c_chinese varchar2(400); v_rl_simple_spell varchar2(400); v_c_simple_spell varchar2(400); v_community_state number;BEGIN v_start := dbms_utility.get_time; IN_COMMUNITY_NAME := '永翔佳苑'; IN_STANDARD_ADDR_NAME := ''; IN_PROD_SPEC_ID := ''; IN_BUSINESS_TYPE := 'Broadband'; IN_USER_TYPE := 1; IN_AREA_ID := 24; IN_CONDITION_TYPE := 0; IN_BEGIN_ROW := 1; IN_END_ROW := 10; v_gpon_not_rsc_spec := 10102008; v_rl_chinese := '%' || IN_STANDARD_ADDR_NAME || '%'; v_c_chinese := '%' || IN_COMMUNITY_NAME || '%'; v_rl_simple_spell := '%%'; v_c_simple_spell := '%%'; v_community_state := 4; EXECUTE IMMEDIATE PG_RMS_FOR_CRM_NEW.SQL_SELECT_RL_COUNT_AREA INTO OUT_ROW_COUNT USING v_community_state,v_rl_chinese,v_c_chinese,v_rl_simple_spell,v_c_simple_spell,in_user_type,v_gpon_not_rsc_spec,IN_AREA_ID; dbms_output.put_line('性能测试1 : ' || (dbms_utility.get_time - v_start) * 10 || ' ms');v_start := dbms_utility.get_time; EXECUTE IMMEDIATE PG_RMS_FOR_CRM_NEW.SQL_INSERT_RL_INFO_AREA USING v_community_state,v_rl_chinese,v_c_chinese,v_rl_simple_spell,v_c_simple_spell,in_user_type,v_gpon_not_rsc_spec,IN_AREA_ID,IN_END_ROW,IN_BEGIN_ROW; dbms_output.put_line('性能测试2 : ' || (dbms_utility.get_time - v_start) * 10 || ' ms');v_start := dbms_utility.get_time; -- Modify the code to output the variable -- DBMS_OUTPUT.PUT_LINE('OUT_ATTRS = ' || OUT_ATTRS); DBMS_OUTPUT.PUT_LINE('OUT_ROW_COUNT = ' || OUT_ROW_COUNT); DBMS_OUTPUT.PUT_LINE('OUT_RET_CODE = ' || OUT_RET_CODE); DBMS_OUTPUT.PUT_LINE('OUT_RET_INFO = ' || OUT_RET_INFO);END;
这种执行的速度是:
性能测试1 : 80 ms
性能测试2 : 30 ms
OUT_ROW_COUNT = 68
OUT_RET_CODE =
然后用在存储过程中,存储过程里面的SQL调用方式一模一样,速度是:
- SQL code
DECLARE IN_COMMUNITY_NAME VARCHAR2(200); IN_STANDARD_ADDR_NAME VARCHAR2(200); IN_PROD_SPEC_ID NUMBER; IN_BUSINESS_TYPE VARCHAR2(200); IN_USER_TYPE NUMBER; IN_AREA_ID NUMBER; IN_CONDITION_TYPE VARCHAR2(200); IN_BEGIN_ROW NUMBER; IN_END_ROW NUMBER; OUT_ATTRS RMLK.PG_RMS_FOR_CRM_NEW.c_list; OUT_ROW_COUNT NUMBER; OUT_RET_CODE VARCHAR2(200); OUT_RET_INFO VARCHAR2(200);BEGIN IN_COMMUNITY_NAME := '永翔佳苑'; IN_STANDARD_ADDR_NAME := ''; IN_PROD_SPEC_ID := ''; IN_BUSINESS_TYPE := 'Broadband'; IN_USER_TYPE := 1; IN_AREA_ID := 24; IN_CONDITION_TYPE := 0; IN_BEGIN_ROW := 1; IN_END_ROW := 10; PG_RMS_FOR_CRM_NEW.SELECT_ADDR_INFO( IN_COMMUNITY_NAME => IN_COMMUNITY_NAME, IN_STANDARD_ADDR_NAME => IN_STANDARD_ADDR_NAME, IN_PROD_SPEC_ID => IN_PROD_SPEC_ID, IN_BUSINESS_TYPE => IN_BUSINESS_TYPE, IN_USER_TYPE => IN_USER_TYPE, IN_AREA_ID => IN_AREA_ID, IN_CONDITION_TYPE => IN_CONDITION_TYPE, IN_BEGIN_ROW => IN_BEGIN_ROW, IN_END_ROW => IN_END_ROW, OUT_ATTRS => OUT_ATTRS, OUT_ROW_COUNT => OUT_ROW_COUNT, OUT_RET_CODE => OUT_RET_CODE, OUT_RET_INFO => OUT_RET_INFO ); -- Modify the code to output the variable -- DBMS_OUTPUT.PUT_LINE('OUT_ATTRS = ' || OUT_ATTRS); DBMS_OUTPUT.PUT_LINE('OUT_ROW_COUNT = ' || OUT_ROW_COUNT); DBMS_OUTPUT.PUT_LINE('OUT_RET_CODE = ' || OUT_RET_CODE);END;性能测试1 : 12170 ms
性能测试2 : 10280 ms
OUT_ROW_COUNT = 68
OUT_RET_CODE =
[解决办法]
你那个语句哪里需要动态？要想快直接写，别用动态SQL。
[解决办法]
一个是动态SQL，一个写死的SQL，当然不一样了。
[解决办法]
- SQL code
[color=#FF0000]PG_RMS_FOR_CRM_NEW.SELECT_ADDR_INFO([/color] IN_COMMUNITY_NAME => IN_COMMUNITY_NAME, IN_STANDARD_ADDR_NAME => IN_STANDARD_ADDR_NAME, IN_PROD_SPEC_ID => IN_PROD_SPEC_ID, IN_BUSINESS_TYPE => IN_BUSINESS_TYPE, IN_USER_TYPE => IN_USER_TYPE, IN_AREA_ID => IN_AREA_ID, IN_CONDITION_TYPE => IN_CONDITION_TYPE, IN_BEGIN_ROW => IN_BEGIN_ROW, IN_END_ROW => IN_END_ROW, OUT_ATTRS => OUT_ATTRS, OUT_ROW_COUNT => OUT_ROW_COUNT, OUT_RET_CODE => OUT_RET_CODE, OUT_RET_INFO => OUT_RET_INFO );--你这个的执行方式和上面直接sql的执行不同吧？他们花费的时间肯定不一样