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

As far as I understand your code, It is updating TBL_IPCOLO_MAST_INFO having SAP_ID = P_SAPID Means It is updating one record and you must be calling the procedure for each record.

It is a good practice of calling the procedure once and update all the record in one go. (In your case 2.5k records must be updated in one call of this procedure only)

For your requirement, Currently, I have updated the procedure code to only execute MERGE statement, which will be same as multiple SQLs in your question for single P_SAPID.

PROCEDURE UPDATE_MST_INFO_BKC (
    P_SAPID IN   NVARCHAR2
) AS
BEGIN
    MERGE INTO TBL_IPCOLO_MAST_INFO I 
    USING (
            SELECT
                RJ_CITY_NAME,
                RJ_NETWORK_ENTITY_ID,
                LATITUDE,
                LONGITUDE,
                RJ_STRUCTURE_TYPE,
                RJ_JC_NAME,
                RJ_JC_CODE,
                '6000' AS COMPANY_CODE,
                RJ_SAPID
            FROM
                STRUCTURE_ENODEB_MAPPING
            WHERE
                RJ_SAPID = P_SAPID
                AND ROWNUM = 1
            )
    O ON ( I.SAP_ID = O.RJ_SAPID )
    WHEN MATCHED THEN 
    UPDATE SET I.CITY_NAME = O.RJ_CITY_NAME,
                I.NEID = O.RJ_NETWORK_ENTITY_ID,
                I.FACILITY_LATITUDE = O.LATITUDE,
                I.FACILITY_LONGITUDE = O.LONGITUDE,
                I.RJ_STRUCTURE_TYPE = O.RJ_STRUCTURE_TYPE,
                I.RJ_JC_NAME = O.RJ_JC_NAME,
                I.RJ_JC_CODE = O.RJ_JC_CODE,
                I.COMPANY_CODE = O.COMPANY_CODE;

END UPDATE_MST_INFO_BKC;

Cheers!!