PL/SQL Code Optimization

I have the following code that takes 27 minutes to execute and display the result. The tables are ...

d_academic & d_personal_academic have 244 records each.

d_config_single has 309 records.

Do give your input to optimize this. Thanks.

Code:

---

set serveroutput on size 1000000

truncate table errors;

DECLARE

        err_num NUMBER;
        err_msg VARCHAR2(100);
        s1 number :=0;
        s2 number :=0;
        s3 number :=0;
        s4 number :=0;       

        CURSOR c1 IS
                SELECT fk_employee_id aa, fk_exam_level bb, fk_qualification cc, group_ofstudy dd,
                                institution ee, month_of_passing ff, year_of_passing gg, grade hh
                                FROM u_port.d_academic;
        CURSOR c2 IS
                SELECT fk_employee_id a, fk_qualif_double_config_id b, fk_course_single_config_id c, fk_inst_single_config_id d,
                                  month_of_passing e, year_of_passing f, grade g
                                FROM d_personal_academic;
        CURSOR c5 IS
                SELECT single_config_id p, value q, code r
                                FROM d_sip_config_single;
BEGIN
        FOR C1_REC IN C1 LOOP
                FOR C2_REC IN C2 LOOP
                        FOR C5_REC IN C5 LOOP
       
                                IF ((C1_REC.ee = C5_REC.q) AND (C2_REC.d = C5_REC.p)) AND
                                        ((C1_REC.aa = C2_REC.a) AND (C1_REC.ff = C2_REC.e) AND
                                        (C1_REC.gg = C2_REC.f) AND (C1_REC.hh = C2_REC.g)) THEN

                                        s1:=s1+1;
                                        dbms_output.put_line('=========================================================================================');
                                        dbms_output.put_line('d_academic empid = '|| C1_REC.aa);
                                        dbms_output.put_line('d_personal_academic empid = '|| C2_REC.a);
       
                                        dbms_output.put('C1_REC.ee = '|| C1_REC.ee);
                                        dbms_output.put_line('  C5_REC.q = '|| C5_REC.q);
                                        dbms_output.put('C2_REC.d = '|| C2_REC.d);
                                        dbms_output.put_line('  C5_REC.p = '|| C5_REC.p);

       
                                        dbms_output.put('C1_REC.ff = '|| C1_REC.ff);
                                        dbms_output.put_line('  C2_REC.e = '|| C2_REC.e);
                                        dbms_output.put('C1_REC.gg = '|| C1_REC.gg);
                                        dbms_output.put_line('  C2_REC.f = '|| C2_REC.f);
                                        dbms_output.put('C1_REC.hh = '|| C1_REC.hh);
                                        dbms_output.put_line('  C2_REC.g = '|| C2_REC.g);
                                        dbms_output.put_line('                        s1 = '||s1);
                                END IF;
                        END LOOP C5;       
                END LOOP C2;
        END LOOP C1;

        dbms_output.put_line('=========================================================================================');       
        dbms_output.put_line('s1 = '||s1);
               
EXCEPTION
        WHEN OTHERS THEN
                err_num := SQLCODE;
                err_msg := SUBSTR(SQLERRM, 1, 100);
                INSERT INTO errors VALUES (err_num, err_msg);
END;
/

---

244*244*309=18,396,624 records in only 27 minutes? You have a pretty fast box! :D
This is the the slowest possible way, IMHO. Use SQL, let server do the dirty job ...

Code:

---

SELECT
  C1_REC.fk_employee_id as "d_academic empid"
  C2_REC.fk_employee_id as "d_personal_academic empid"
  C1_REC.institution ee,
  C5_REC.value q,
  C2_REC.fk_inst_single_config_id d,
  C5_REC.single_config_id p,
  C1_REC.month_of_passing ff,
  C2_REC.month_of_passing e,
  C1_REC.year_of_passing gg,
  C2_REC.year_of_passing f,
  C1_REC.grade hh,
  C2_REC.grade g
FROM
  u_port.d_academic C1_REC,
  d_personal_academic C2_REC,
  d_sip_config_single C5_REC
WHERE C1_REC.institution = C5_REC.value
  AND C2_REC.fk_inst_single_config_id = C5_REC.single_config_id
  AND C1_REC.fk_employee_id = C2_REC.fk_employee_id
  AND C1_REC.month_of_passing = C2_REC.month_of_passing
  AND C1_REC.year_of_passing = C2_REC.year_of_passing
  AND C1_REC.grade = C2_REC.grade

---

When u need cursors, then make them depended, like the example here for cursor C2:
CURSOR c1 IS
SELECT fk_employee_id aa, fk_exam_level bb, fk_qualification cc, group_ofstudy dd,
institution ee, month_of_passing ff, year_of_passing gg, grade hh
FROM u_port.d_academic;
CURSOR c2(p_aa d_personal_academic.fk_employee_id%type
, p_ff d_personal_academic.month_of_passing%type
, p_gg d_personal_academic.year_of_passing%type
, p_hh d_personal_academic.grade%type)
IS
SELECT fk_employee_id a, fk_qualif_double_config_id b, fk_course_single_config_id c, fk_inst_single_config_id d,
month_of_passing e, year_of_passing f, grade g
FROM d_personal_academic
where fk_employee_id = p_aa
and month_of_passing = p_ff
and year_of_passing = p_gg
and grade = p_hh;
CURSOR c5 IS
SELECT single_config_id p, value q, code r
FROM d_sip_config_single;
BEGIN
FOR C1_REC IN C1 LOOP
FOR C2_REC IN C2(C1_REC.aa, C1_REC.ff, C1_REC.gg, C1_REC.hh) LOOP
FOR C5_REC IN C5 LOOP
.......

Thanks for the input. Appreciate it!

Quote:

---

Originally posted by bensr
When u need cursors, then make them depended, like the example here for cursor C2:
CURSOR c1 IS
SELECT fk_employee_id aa, fk_exam_level bb, fk_qualification cc, group_ofstudy dd,
institution ee, month_of_passing ff, year_of_passing gg, grade hh
FROM u_port.d_academic;
CURSOR c2(p_aa d_personal_academic.fk_employee_id%type
, p_ff d_personal_academic.month_of_passing%type
, p_gg d_personal_academic.year_of_passing%type
, p_hh d_personal_academic.grade%type)
IS
SELECT fk_employee_id a, fk_qualif_double_config_id b, fk_course_single_config_id c, fk_inst_single_config_id d,
month_of_passing e, year_of_passing f, grade g
FROM d_personal_academic
where fk_employee_id = p_aa
and month_of_passing = p_ff
and year_of_passing = p_gg
and grade = p_hh;
CURSOR c5 IS
SELECT single_config_id p, value q, code r
FROM d_sip_config_single;
BEGIN
FOR C1_REC IN C1 LOOP
FOR C2_REC IN C2(C1_REC.aa, C1_REC.ff, C1_REC.gg, C1_REC.hh) LOOP
FOR C5_REC IN C5 LOOP
.......

---

The above approach has optimized my PL/SQL code from 6 hours to about 15 minutes.