How can I optimize a loop, which is calling an object in Oracle?

Question

Below is the code which needs optimization.

for i in 1 .. p_in_util_data_list(j).factlist.count LOOP
            SELECT count(*)
              INTO v_non_factor_exists
              FROM engine_usage_factors
             WHERE usage_month = v_usage_month
               AND contract_seq_id = p_in_contractId
               AND engine_serial_number = p_in_util_data_list(j).esn
               AND nvl(upper(subfleet_id), 'X') =
                   nvl(upper(p_in_util_data_list(j).fleet), 'X')
               AND nvl(upper(tail_number), 'X') =
                   nvl(upper(p_in_util_data_list(j).tail), 'X')
               AND nvl(upper(factor_name), 'X') =
                   nvl(upper(p_in_util_data_list(j).factlist(i).name), 'X')
               AND avg_flag = 'N'
               AND recon_ind = 0;

            IF v_non_factor_exists > 0 THEN
              DELETE FROM engine_usage_factors
               WHERE usage_month = v_usage_month
                 AND contract_seq_id = p_in_contractId
                 AND engine_serial_number = p_in_util_data_list(j).esn
                 AND nvl(upper(subfleet_id), 'X') =
                     nvl(upper(p_in_util_data_list(j).fleet), 'X')
                 AND nvl(upper(tail_number), 'X') =
                     nvl(upper(p_in_util_data_list(j).tail), 'X')
                 AND nvl(upper(factor_name), 'X') =
                     nvl(upper(p_in_util_data_list(j).factlist(i).name),
                         'X')
                 AND avg_flag = 'N'
                 AND recon_ind = 0;
              COMMIT;
            END IF;

            IF UPPER(P_IN_UTIL_DATA_LIST(J).FACTLIST(I).NAME) = 'THRUST' THEN
              V_VALUE := 0;
            ELSE
              V_VALUE := P_IN_UTIL_DATA_LIST(J).FACTLIST(I).VALUE;
            END IF;


            IF UPPER(p_in_util_data_list(j).factlist(i).name) = 'THRUST' THEN



              UPDATE engine_usage
                 SET THRUST_RATING    = p_in_util_data_list(j).factlist(i)
                                        .value,
                     last_updated_by  = p_in_login_id,
                     last_update_date = sysdate
               WHERE usage_month = v_usage_month
                 AND engine_serial_number =
                     trim(p_in_util_data_list(j).esn)
                 AND recon_ind = 0
                 AND from_date = v_from_date
                 AND contract_seq_id = p_in_contractId 
                 AND hybrid_payment_type = p_in_billingType;
              COMMIT;



              v_value := 0;

            END IF;

            INSERT INTO engine_usage_factors
              (usage_month,
               contract_seq_id,
               engine_serial_number,
               subfleet_id,
               tail_number,
               factor_name,
               factor_value,
               recon_ind,
               from_date,
               avg_flag,
               created_by,
               created_date,
               last_updated_by,
               last_update_date)
            values
              (v_usage_month,
               p_in_contractId,
               p_in_util_data_list(j).esn,
               p_in_util_data_list(j).fleet,
               NVL(p_in_util_data_list(j).tail, 'DUMMY'),
               p_in_util_data_list(j).factlist(i).name,
               v_value,
               0,
               v_from_date,
               'N',
               p_in_login_id,
               sysdate,
               p_in_login_id,
               sysdate);

            commit;
          END LOOP;

This loop is being called 700 times and takes alot of time.

Start trying to identify the slowest parts (the select, delete, update ?); also, are you sure you need to loop that way or can you re-think your solution with some bulk operations, with different structures? And what happens if a statement gives an error after a commit? You would have a partial commit, is this what you need? — Aleksej
– Aleksej, Commented Oct 26, 2016 at 9:18
@Aleksej- Thank you for the suggestion! Can you please tell me, how can I replace loops? The loop might execute ranging from 0 to 700. — CodERORR
– CodERORR, Commented Oct 26, 2016 at 9:47
Have a look at FORALL and check if you can rewrite your code to use it — Aleksej
– Aleksej, Commented Oct 26, 2016 at 12:50
How is p_in_util_data_list(j) produced? Sounds like it's inside another loop. It's possible that you could refactor things to make the outer and inner loops more peformant, if you could provide the extra detail. — Boneist
– Boneist, Commented Oct 26, 2016 at 13:37

Michael Piankov · Accepted Answer · 2016-10-26 09:22:10Z

1

Well I see you have a huge loop with delete update insert.

After any operaion you call commit. In oracle better to use just one commit on the end of transaction.
Besides You update row by row but better try to update, insert and delete in one merge statement.

answered Oct 26, 2016 at 9:22

Michael Piankov

1,9971 gold badge11 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How can I optimize a loop, which is calling an object in Oracle?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related