-
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;
/
Last edited by ggnanaraj; 01-17-2003 at 05:27 AM.
-
244*244*309=18,396,624 records in only 27 minutes? You have a pretty fast box!
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
Ales The whole difference between a little boy and an adult man is the price of toys
-
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
.......
Regards
Ben de Boer
-
thanks
Thanks for the input. Appreciate it!
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|