PL/SQL Proc performance tuning

Question

I have a loop inside the proc which loops through an ID. An CDID can be of 4 types:

CDID      TYPE
123     AA
456     BB
789     CC
999     DD

There are 4 main blocks of IF for each type. Although it is not necessary for an CDID to have all 4 types, and each main IF block consists of nested if blocks. Inside the nested ifs, I am inserting values into 4 variables(not a table) using a select query. And at the end of all 4 IF blocks, there is a single update statement which takes the values of 4 variables and updates the table.

Below is the proc structure for your reference:

CREATE OR REPLACE PROC MY_PROC
a varchar2;
b varchar2;
c varchar2;
d varchar2;
v_cnt number
BEGIN
FOR I IN (SELECT CDID FROM TABLE_A where column is null)    --this table contains only 1 row for each id
BEGIN
    select count(*) into v_cnt from TABLE_B where cdid=i.cdid and type='AA';
    if v_count > 0 then
        nested ifs
        SELECT a,b,c,d INTO a,b,c,d from TABLE_B where cdid=i.cdid and type='AA'...;
        end ifs...;

    if v_count = 0 then --if count is zero of TYPE:AA

    select count(*) into v_cnt from TABLE_B where cdid=i.cdid and type='BB';  
    if v_count > 0 then
       nested ifs
       select a,b,c,d INTO a,b,c,d from TABLE_B where cdid=i.cdid and type='BB'...;
       end ifs...;

    if v_count = 0 then --if count is zero of TYPE:BB
        same thing continues for TYPES CC AND DD.
    end if;
END;
UPDATE TABLE_A
SET COLUMN1=A,
    COLUMN2=B,
    COLUMN3=C,
    COLUMN4=D
where cdid=i.cdid;
END LOOP;
END;

For approx. 30k-35k records, the proc takes 40 mins to execute. In the above proc, for each cdid, it can check multiple if.

I am not able to identify how to fine tune this proc. How to bulk process the records?

Answer 1

Untested but following on from what @GloezTrol said in the comments something like the below should work. Use a single cursor ordered by type so that it process the IF's in the correct order. Then only loop till you find the record you want and exit.

CREATE OR REPLACE PROC MY_PROC as
a varchar2(10); -- size these appropriately
b varchar2(10);
c varchar2(10);
d varchar2(10);
v_cnt number;

cursor loop_cur is
select *
from TABLE_B 
where cdid=i.cdid
and type in ('AA','BB','CC','DD')
order by type asc;

BEGIN
  FOR I IN (SELECT CDID FROM TABLE_A where column is null)    --this table contains only 1 row for each id
    BEGIN
      -- good practice to re-initialize variables at start of loop
      a := null;
      b := null;
      c := null;
      d := null;

      for v_row in loop_cur loop
        if v_row.type = 'AA' then
          nested ifs
            a := v_row.a;
            b := v_row.b;
            c := v_row.c;
            d := v_row.d;
            exit; -- get out of the loop if there was a 'AA' 
          end ifs...;
         if v_row.type = 'BB' then
           nested ifs
             a := v_row.a;
             b := v_row.b;
             c := v_row.c;
             d := v_row.d;
             exit; -- get out of the loop if there was a 'BB' 
           end ifs...;
         end if
         repeat for 'CC' and 'DD'
       end loop;
     END;
    UPDATE TABLE_A
    SET COLUMN1=A,
        COLUMN2=B,
        COLUMN3=C,
        COLUMN4=D
    where cdid=i.cdid;
  END LOOP;
END;