Stored procedure is taking too much time to update the table columns

Question

I have created a stored procedure which is taking too much of time to update the columns of the table. Say 3 hrs to update 2.5k records out of 43k records.

So can I reduce the time of updating the records. Below is my logic for the same.

procedure UPDATE_MST_INFO_BKC
  (
    P_SAPID IN NVARCHAR2
  )
  as
  v_cityname varchar2(500):='';
  v_neid varchar2(500):='';
  v_latitude varchar2(500):='';
  v_longitude varchar2(500):='';
  v_structuretype varchar2(500):='';
  v_jc_name varchar2(500):='';
  v_jc_code varchar2(500):='';
  v_company_code varchar2(500):='';
  v_cnt number :=0;
  
  begin
  select count(*) into v_cnt from structure_enodeb_mapping where  RJ_SAPID=P_SAPID  and rownum=1;
  
  if v_cnt > 0 then 
  begin
  
   select RJ_CITY_NAME, RJ_NETWORK_ENTITY_ID,LATITUDE,LONGITUDE,RJ_STRUCTURE_TYPE,RJ_JC_NAME,RJ_JC_CODE,'6000' 
   into v_cityname,v_neid,v_latitude, v_longitude, v_structuretype,v_jc_name,v_jc_code,v_company_code from structure_enodeb_mapping where RJ_SAPID=P_SAPID  and rownum=1;

        update tbl_ipcolo_mast_info set 
        
        CITY_NAME                   = v_cityname,
        NEID                        = v_neid,
        FACILITY_LATITUDE           = v_latitude,
        FACILITY_LONGITUDE          = v_longitude,
        RJ_STRUCTURE_TYPE           = v_structuretype,
        RJ_JC_NAME                  = v_jc_name,  
        RJ_JC_CODE                  = v_jc_code,  
        COMPANY_CODE                = v_company_code
        where SAP_ID=P_SAPID;
  
  end;
  end if;   

  end UPDATE_MST_INFO_BKC;

What adjustments can I make to this?

Answer 1

3 hours? That's way too much. Are sap_id columns indexed? Even if they aren't, data set of 43K rows is just too small.

How do you call that procedure? Is it part of another code, perhaps some unfortunate loop which does something row-by-row (which is, in turn, slow-by-slow)?

A few objections:

• are all those variables' datatypes really varchar2(500)? Consider declaring them so that they'd take table column's datatype, e.g. v_cityname structure_enodeb_mapping.rj_city_name%type;. Also, there's no need to explicitly say that their value is null (:= ''), it is so by default
• select statement which checks whether there's something in the table for that parameter's value should be rewritten to use EXISTS as it should perform better than rownum = 1 condition you used.
  • also, consider using exception handlers (no-data-found if there's no row for a certain ID; too-many-rows if there are two or more rows)
• select statement that collects data into variables has the same condition; do you really expect more than a single row for each ID (passed as a parameter)?

Anyway, the whole procedure's code can be shortened to a single update statement:

update tbl_ipcolo_mst_info t set
  (t.city_name, t.neid, ...) = (select s.rj_city_name, 
                                       s.rj_network_entity_id, ...
                                from structure_enodeb_mapping s
                                where s.rj_sapid = t.sap_id
                               )
where t.sap_id = p_sapid;

If there is something to be updated, it will be. If there's no matching t.sap_id, nothing will happen.