同一个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;