Re: Efficiency of Procedures/Functions Over In-line Code ?

Hi,

I agree with all the wise comments from Sybrand, Thomas and others on the careful use of functions.

Here is a real live example, with timings, of how I've used a function to replace an outer-join on a view ( which can be notoriously inefficient ) and obtained a performance improvement of 13889x in CPU time and 1173x in elapsed time ( down from 3 minutes to virtually instant ) in the fetch statistics. As the query was intended to be run online, many times per day, the original implementation was unuseable. The example comes from some customisation we did to Oracle Financials.

Original query was ....

    SELECT ...

     FROM PER_ASSIGNMENTS_F A,
          WTE_VALUES V
     WHERE V.ASSIGNMENT_ID(+) = A.ASSIGNMENT_ID
     AND A.ASSIGNMENT_ID = 5004

gives statistics of ...

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------ -
Parse 1 0.06 0.06 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 138.89 175.98 31378 931451 3 2
------- ------ -------- ---------- ---------- ---------- ---------- ------ -
total 3 138.95 176.04 31378 931451 3 2

But creating a function ....

     CREATE OR REPLACE FUNCTION LOOKUP_WTE (L_ASSIGNMENT_ID VARCHAR2)
            RETURN VARCHAR2 IS


     --

        RETURN_VALUE VARCHAR2(80);


     --

        CURSOR C1 IS
           SELECT WTE_VALUE
            FROM  WTE_VALUES
           WHERE  ASSIGNMENT_ID = L_ASSIGNMENT_ID;


     --

         BEGIN


     --

           RETURN_VALUE := NULL;


     --

           IF ( L_ASSIGNMENT_ID IS NOT NULL ) THEN
              OPEN C1;
              FETCH C1 INTO RETURN_VALUE;
              CLOSE C1;
           END IF;


     --

           RETURN RETURN_VALUE;


     --

        END;

and changing the query to ...

      SELECT A.ASSIGNMENT_ID, LOOKUP_WTE(A.ASSIGNMENT_ID)
      FROM PER_ASSIGNMENTS_F A

gives statistics ...

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------ -
Parse 1 1.40 1.54 5 0 10 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.01 0.15 9 29 3 2
------- ------ -------- ---------- ---------- ---------- ---------- ------ -
total 3 1.41 1.69 14 29 10 2

Dave.

--
Remove "nospam" from my address to reply by email