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 ?

Example

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;

Solution

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;