CODEWITHSUNDEEP
sqlplsql
vikeng21
vikeng21

Reputation: 531

PL/SQL block taking lot of time to execute

I am learning PL/SQL and have written a PL/SQL block which i have pasted below. The block is running as per expected but it is taking a huge amount of time to complete the process. To check i ran with 10 records in both tables it took 4 minutes. After that i tried to run it on a huge volume of data it did not complete even after 5 hours. Can anyone please guide me as to whats the issue here and how i can increase the performance. below is my PL/SQL block.

EDIT: There is no index created on the client_id in the client_trans_vk1 for the update query. Is that the culprit.

DECLARE
v_client_id test_vk.client_id%TYPE;
v_trans_amount account_bal.Bal_Am%TYPE;

cursor c_client
is select client_id,TRANS_AM as transaction_amount  from test_vk;

begin
  OPEN  c_client;
  LOOP
    FETCH c_client INTO v_client_id,v_trans_amount;
    BEGIN
    EXIT WHEN c_client%NOTFOUND;
     update client_trans_vk1 ca
       set ca.bal_amt = 0, ca.additional_AM = (ca.additional_AM + v_trans_amount) 
       where ca.client_id = v_client_id;
     commit;
    END;
  end LOOP;
  CLOSE c_client
end;

Upvotes: 0

Views: 227

Answers (1)

Mikhail
Mikhail

Reputation: 1560

There is no EXIT from LOOP statement, I'm surprised it even worked, it basically looped
infinitely on the last row. You need to exit the loop, after you've fetched all rows from cursor, like this:

DECLARE
v_client_id test_vk.client_id%TYPE;
v_trans_amount account_bal.Bal_Am%TYPE;

cursor c_client
is select client_id,TRANS_AM as transaction_amount  from test_vk;

begin
  OPEN  c_client;
  LOOP
    FETCH c_client INTO v_client_id,v_trans_amount;
    EXIT WHEN c_client%NOTFOUND;
    BEGIN
     update client_trans_vk1 ca
       set ca.bal_amt = 0, ca.additional_AM = (ca.additional_AM + v_trans_amount) 
       where ca.client_id = v_client_id;
     commit;
    END;
  end LOOP;
  CLOSE c_client
end;

Upvotes: 1

Related Questions