Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2012
    Posts
    36

    Unanswered: Improving the performance of a procedure using FOR loop

    Dear Experts,

    I have created a procedure, which is consuming a lot of hours, i want to imrove the performance by removing 'FETCH' and including 'FOR' Loop.

    But im struck with implementing mulitple cursor inside a single FOR loop statement.

    Please advise,

    here is the query:

    CREATE OR REPLACE procedure SYSTEM.Fn_Matching_Entity_Tmp_01
    as
    cursor c1 is
    SELECT * FROM Tbl_Entity_Tmp01 ;--WHERE TRIM(REC_TYPE) ='DE';
    -- TYPE dept_tab_type IS TABLE OF c1%ROWTYPE;
    cursor c2 is
    SELECT * FROM Tbl_Entity_Tmp01 ;--WHERE TRIM(REC_TYPE) ='DE';

    C_ENTITYSET_1 c1%rowtype ;
    C_ENTITYSET_2 c2%rowtype ;

    l_per number;

    v_start number;
    v_finish number;
    v_int number;
    VnbrTmp number;
    VMatchingPer number;

    begin
    begin

    delete from Tbl_Matching_Tmp01 where Trim(recordtype) ='Entity';
    commit;

    end;


    open c1;

    loop

    fetch c1 into C_ENTITYSET_1;
    exit when c1%notfound;

    VnbrTmp :=0;
    VMatchingPer :=0;

    open c2;

    loop

    fetch c2 into C_ENTITYSET_2;
    exit when c2%notfound;

    VnbrTmp :=0;
    VMatchingPer :=0;

    VMatchingPer := utl_match.edit_distance_similarity( upper(C_ENTITYSET_1.ENTITY_NAME_ARABIC_TMP) ,upper(C_ENTITYSET_2.ENTITY_NAME_ARABIC_TMP));

    /* if VMatchingPer >=30

    then */

    SELECT count(*) into VnbrTmp from Tbl_Matching_Tmp01 where
    (C_ENTITYSET_1.ENTITY_ID <> 'XXXXXX' OR C_ENTITYSET_2.ENTITY_ID <> 'XXXXXX')
    AND
    ((C_ENTITYSET_1.ENTITY_ID = Code1 AND C_ENTITYSET_2.ENTITY_ID = Code2
    AND
    VMatchingPer =MatchingPer)
    OR
    (C_ENTITYSET_1.ENTITY_ID = Code2 AND C_ENTITYSET_2.ENTITY_ID = Code1
    AND
    VMatchingPer =MatchingPer) );
    /* end if;*/

    if VnbrTmp =0
    then
    insert into Tbl_Matching_Tmp01 values ('Entity',C_ENTITYSET_1.ENTITY_ID,
    C_ENTITYSET_1.ENTITY_NAME_ARABIC,C_ENTITYSET_2.ENT ITY_ID,
    C_ENTITYSET_2.ENTITY_NAME_ARABIC, 'Fn_Matching_Entity_Tmp_01',
    VMatchingPer,
    1);

    end if;

    commit;
    end loop;
    close c2;

    end loop;


    close c1;






    end;
    /

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    row by row is slow by slow

    never do in PL/SQL that which can be done in plain SQL

    Code:
    CREATE OR replace PROCEDURE SYSTEM.Fn_matching_entity_tmp_01 
    AS 
      CURSOR c1 IS 
        SELECT * 
        FROM   tbl_entity_tmp01; --WHERE TRIM(REC_TYPE) ='DE'; 
      -- TYPE dept_tab_type IS TABLE OF c1%ROWTYPE; 
      CURSOR c2 IS 
        SELECT * 
        FROM   tbl_entity_tmp01; --WHERE TRIM(REC_TYPE) ='DE'; 
      c_entityset_1 c1%ROWTYPE; 
      c_entityset_2 c2%ROWTYPE; 
      l_per         NUMBER; 
      v_start       NUMBER; 
      v_finish      NUMBER; 
      v_int         NUMBER; 
      vnbrtmp       NUMBER; 
      vmatchingper  NUMBER; 
    BEGIN 
        BEGIN 
            DELETE FROM tbl_matching_tmp01 
            WHERE  Trim(recordtype) = 'Entity'; 
    
            COMMIT; 
        END; 
    
        OPEN c1; 
    
        LOOP 
            FETCH c1 INTO c_entityset_1; 
    
            exit WHEN c1%NOTFOUND; 
    
            vnbrtmp := 0; 
    
            vmatchingper := 0; 
    
            OPEN c2; 
    
            LOOP 
        FETCH c2 INTO c_entityset_2; 
    
        exit WHEN c2%NOTFOUND; 
    
        vnbrtmp := 0; 
    
        vmatchingper := 0; 
    
        vmatchingper := utl_match.Edit_distance_similarity(Upper( 
                        c_entityset_1.entity_name_arabic_tmp) 
    , 
                                    Upper(c_entityset_2.entity_name_arabic_tmp)); 
    
    /* if VMatchingPer >=30 
    
    then */ 
    SELECT Count(*) 
    INTO   vnbrtmp 
    FROM   tbl_matching_tmp01 
    WHERE  ( c_entityset_1.entity_id <> 'XXXXXX' 
              OR c_entityset_2.entity_id <> 'XXXXXX' ) 
           AND ( ( c_entityset_1.entity_id = code1 
                   AND c_entityset_2.entity_id = code2 
                   AND vmatchingper = matchingper ) 
                  OR ( c_entityset_1.entity_id = code2 
                       AND c_entityset_2.entity_id = code1 
                       AND vmatchingper = matchingper ) ); 
    
    /* end if;*/ 
    IF vnbrtmp = 0 THEN 
      INSERT INTO tbl_matching_tmp01 
      VALUES      ('Entity', 
                   c_entityset_1.entity_id, 
                   c_entityset_1.entity_name_arabic, 
                   c_entityset_2.ent ity_id, 
                   c_entityset_2.entity_name_arabic, 
                   'Fn_Matching_Entity_Tmp_01', 
                   vmatchingper, 
                   1); 
    END IF; 
    
    COMMIT; 
    END LOOP; 
    
    CLOSE c2; 
    END LOOP; 
    
    CLOSE c1; 
    END; 
    
    /
    which is easier to read & understand; your code or mine?
    Last edited by anacedent; 09-21-13 at 08:47.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •