Individual Record Logging While Handling Large no of records

Question

I am having a procedure that will assign Products against Existing List of Orders.

For rec_ord in(Select Order_ID,Order_Prop1,Order_Prop2,<some more columns> 
               from Order_Master Where <Some Conditions>)
Loop
<Step-1:Do some Processing on Order_Prop1,Order_Prop2>
[Log the Processing Result]
For rec_prod in (Select Prod_ID,Prod_Prop1,Prod_Prop2,<some more columns> 
                 from Product_Master 
                 Where Prod_Prop1 = Ord_Prop1
                 and <Some Conditions>)
Loop
<Step-2:Do Some Processing using Prod_Prop2 and Order_Prop2>
[Log the Processing Result]
<Decide Whether to Assign or Not>
[Log the assignment or non-assignment with reason]
End Loop
End Loop

I tried below 2 methods.

Bulk Collect:I Combined Step-1&2 in the single query by joining Order_Master & Product_Master. And then Using Bulk-Collect to insert the assignment. However I am losing the Logging & Tracking of individual Record.
For Loop: I used For Loop as given above. But this is taking way too long. Increasing my Execution time by many times.

I want the Processing to be fast along with the logging & tracking. Any Help is appreciated.

Thanx in advance.

One More Info. No of Records in Order_Master is around 50-60k & Product_Master is around 200-220k — Debabrata, Nov 10 '16 at 06:29
What makes option 2 slow? Is it also slow if you remove the logging? — Rene, Nov 10 '16 at 07:10
No. Without logging part, it's much faster. Though not fast as option 1. — Debabrata, Nov 10 '16 at 07:11
Dont know about that. The Logging Program is contains a single insert statement and takes few parameters as input. — Debabrata, Nov 10 '16 at 09:16

score 0 · Answer 1 · answered Nov 12 '16 at 20:59

For me, the problem here seems to be that you try to combine mass-row processing with single-row logging. This leads to very poor performance, as Oracle has to switch between PL/SQL and SQL engine constantly.

Therefore, a feasible solution would be to collect the logging information in an array TYPE t_logs IS TABLE OF reasonable_rec_log_type and pass it to a logging procedure at once or in chunks from time to time (in the outer loop?), depending on the prospective table dimensions.

If you decide for a bulk collect solution, using FORALL (multiple times) would also be possible if you did the Inserts for logging yourself, as it will not speed up when calling a procedure - it's meant to generate the DML statements that would have been executed one row at a time and execute them altogether.

Individual Record Logging While Handling Large no of records

1 Answers1