Forum Stats

  • 3,769,367 Users
  • 2,252,958 Discussions
  • 7,875,006 Comments

Discussions

Is there any further option to optimize the loop session

SHANOJ
SHANOJ Member Posts: 237 Blue Ribbon

BEGIN

  FOR row_id IN acct_list LOOP

    FOR lookup_data IN flex_loans_tmp LOOP

      v_count_commit := v_count_commit + 1;

      v_lookup_str6 := ' ';

      IF ( ( TRIM(lookup_data.contract_ref_no) IS NOT NULL ) AND ( TRIM(row_id.string_11) LIKE '%'

                                                   || TRIM(lookup_data.contract_ref_no

                                                   )

                                                   || '%' ) ) THEN

        v_lookup_str6 := lookup_data.facility_name;

      ELSIF ( TRIM(row_id.reference) LIKE '%'

                        || lookup_data.contract_ref_no

                        || '%' ) THEN

        v_lookup_str6 := lookup_data.facility_name;

      ELSIF ( ( TRIM(lookup_data.cusip_no) IS NOT NULL ) AND ( trim(row_id.string_13) =

        trim(lookup_data.cusip_no) ) ) THEN

        v_lookup_str6 := lookup_data.facility_name;

      ELSIF ( ( TRIM(lookup_data.ft_ref) IS NOT NULL ) AND ( TRIM(row_id.reference) LIKE '%'

                                                || TRIM(lookup_data.ft_ref)

                                                || '%' ) ) THEN

        v_lookup_str6 := lookup_data.facility_name;

      ELSE

        v_lookup_str6 := ' ';

      END IF;


      IF ( TRIM(v_lookup_str6) IS NOT NULL ) THEN

        v_match_found := 1;

        UPDATE item

        SET

          string_6 = v_lookup_str6,

          date_21 = SYSDATE,

          flag_21 = v_flag_1

        WHERE

          corr_acc_no = row_id.corr_acc_no

          AND gin = row_id.gin

          AND flag_21 = 0

          AND TRIM(string_6) IS NULL;


        EXIT;

      END IF;


      IF v_count_commit > 500 THEN

        COMMIT;

        v_count_commit := 0;

      END IF;

    END LOOP;


    IF ( v_match_found = 0 ) THEN

      UPDATE item

      SET

        flag_21 = 2

      WHERE

        corr_acc_no = row_id.corr_acc_no

        AND gin = row_id.gin

        AND flag_21 = 0

        AND TRIM(string_6) IS NULL;


    END IF;


    COMMIT;

  END LOOP;

END;

/

Tagged:

Best Answer

  • jflack
    jflack Member Posts: 1,516 Bronze Trophy
    Accepted Answer

    Of course, the best way to optimize a loop is not to loop at all. We'd need a better description of your tables and SELECTs to help, but the idea would be to do everything in SQL instead of PL/SQL. A single UPDATE command or a MERGE command would perform better, if you can figure out how to write it.

    SHANOJ

Answers

  • jflack
    jflack Member Posts: 1,516 Bronze Trophy
    Accepted Answer

    Of course, the best way to optimize a loop is not to loop at all. We'd need a better description of your tables and SELECTs to help, but the idea would be to do everything in SQL instead of PL/SQL. A single UPDATE command or a MERGE command would perform better, if you can figure out how to write it.

    SHANOJ