Move unnecessary statements out of Loops
Sometimes it may be necessary to obtain additional information via method calls or cursors while iterating through a loop to perform a certain task. It is always a good practice to consider the fetching of additional information is really necessary to be done for each and every iteration. Is it possible to minimize the number of method calls or OPEN-FETCH-CLOSE of cursors ?
Invoice and invoiceItem Lus has a master-detail relationship. One invoice can contain many invoice items.
Consider the following procedure where, the invoice items belonging to a single invoice, is iterated over and some task is performed. To do the task, additional information from invoice Lu (master) is needed and fetched via a method call Invoice_API.Get_Adv_Inv. This is done inside the loop, but the information returned is common for every invoice item iterated over. The method call causes a performance overhead which can be minimized. The time taken to retrieve data via the method once, is multiplied by the number of iterations, and it will contribute to the actual execution time of the procedure.
PROCEDURE Unnecessary_Statements ( company_ IN VARCHAR2, invoice_id_ IN NUMBER ) IS adv_invoice_ VARCHAR2(5); CURSOR get_invoice_items IS SELECT * FROM invoice_item_tab it WHERE it.company = company_ AND it.invoice_id = invoice_id_; BEGIN FOR rec_ IN get_invoice_items LOOP -- Method call will return the same information for each row -- Since company, invoice_id is common for each invoice_item adv_invoice_ := Invoice_API.Get_Adv_Inv(rec_.company, rec_.invoice_id); -- Do something using the adv_invoice information END LOOP; END Unnecessary_Statements;
Since the information is common for all invoice items, the method call can be placed outside the loop as shown below. It is obvious that the number of executions of the method call is reduced to one, irrespective of the number of invoice items, the invoice contains and thus reduces the execution time.
PROCEDURE Statements_Out_Of_Loop ( company_ IN VARCHAR2, invoice_id_ IN NUMBER ) IS adv_invoice_ VARCHAR2(5); CURSOR get_invoice_items IS SELECT * FROM invoice_item_tab it WHERE it.company = company_ AND it.invoice_id = invoice_id_; BEGIN -- Method call placed outside the loop adv_invoice_ := Invoice_API.Get_Adv_Inv(company_, invoice_id_); FOR rec_ IN get_invoice_items LOOP -- Do something using the adv_invoice information END LOOP; END Statements_Out_Of_Loop;