PL/SQL Code Optimization
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: PL/SQL Code Optimization

  1. #1
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865

    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 04:27 AM.

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    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

  3. #3
    Join Date
    Feb 2001
    Posts
    180
    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

  4. #4
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865

    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
  •  



Click Here to Expand Forum to Full Width