RE: Which one is faster ???

From: Rudy Zung <rzung_at_printcafe.com>
Date: Tue, 08 Jul 2003 14:51:08 -0700
Message-ID: <F001.005C3E7B.20030708144425@fatcity.com>

Alright, since I was intrigued by this, I've decided to take some empirical measurements.

I compare:

procedure with out parameter
procedure with out nocopy parameter
function

The body of the procedure is to catenation of 40 characters 30 times (to yield a 1200 character varchar). In the case of the procedures, the OUT parameter is assigned as the result of the catenation:

   r := '****************************************' ||
        '****************************************' ...

In the case of the function, the "r :=" removed, and a "return()" is used in its place.

For each procedure or function, I execute it within an anonymous block within 3 separate for loops, where the for loop ranges from 1 .. 1000000. I pick up the DBMS_UTILITY.GET_TIME immediate before and immediate after the for loop, giving me 3 timing information for each procedure/function, kind of like this:

declare

   i number;
   r varchar2(4000);
   s number;
   e number;
begin

   s := dbms_utility.get_time;
   for i in 1 .. 1000000
   loop

      <call procedure: p(r);
       or call function:  r := f;>

   end loop;
   e := dbms_utility.get_time;
   dbms_output.put_line(

<subtract startTime from endTime>);

<the chunk above is then cut-and-paste repeated 2 more times>
end;

The result (in seconds):
TIME METHOD
----- ------

16.75  Procedure OUT
16.68  Procedure OUT
16.76  Procedure OUT
15.73  Procedure OUT NOCOPY
15.76  Procedure OUT NOCOPY
16.85  Procedure OUT NOCOPY
17.50  Function
17.43  Function
17.41  Function

Alright, so it looks like on average, a procedure using OUT NOCOPY parameters is marginally fastest across one million calls, followed by procedure using copy-in- copy-out parameters, then slowest is function.

Since all the procedure/function bodies are virtually identical, the differences in time are most likely attributable to transferring data off the stack frame into the assignment variable, and in our case we are transferring about 1K of varchar2 data per call; with that in mind, the difference from the fast OUT NOCOPY versus the slow function is less than 2 seconds for one million calls, which means for most intents and purposes, this is a negligible number unless you are returning humungous amounts of data in really long varchars or PL/SQL tables. This implies also that if you are simply returning single scalar values like a number, a date, or a couple of characters, the performance degradation/improvement is hovering on negligible (there was some blurb at
http://www.databasejournal.com/features/oracle/article.php/1558021 when Oracle introduced NOCOPY that indicated Oracle benchmarked 30% to 200% improvements when PL/SQL tables were passed as NOCOPY; but keep in mind that if the PL/SQL table is passed as IN OUT without NOCOPY, then the PL/SQL table would have to be copied-in first, followed by a copy-out.)

Regards.

...Rudy

-----Original Message-----
Sent: Tuesday, July 08, 2003 1:29 PM
To: Multiple recipients of list ORACLE-L

Well, without knowing how Oracle implemented their PL/SQL engine, and without empirical data (which truthfully I have not bothered to collect) this is a hard question to answer. However, with my little knowledge of compiler design, I'll give you my guess as something to think about and you can run with it.

PL/SQL is basically an interpreted language, which means it doesn't get that directly close to the assembly/machine code like a traditional compiled language does. For this reason, I don't think Oracle is bothering to optimize the PL/SQL engine that heavily into mapping variables onto registers; I'll assume that it's all basically allocated from Oracle's heap that it gets from the OS.

Since you are contemplating a procedure versus a function where all things being equal, a resonable assumption would be that your procedure must have at least 1 OUT parameter that would take the place of the returned value from the function.

Now here, it depends largely on how Oracle has implemented their PL/SQL parameter passing and management of PL/SQL stack. For a function, typically in other languages the return value is computed and stored on the stack or register, and after the function terminates, the return value is copied over to the variable that is on the left-hand-side of the assignment operator. For a procedure with a pass-by-reference variable, the original variable is accessed (since it's pass-by-reference) so there would be no need for a final copy operation. In this scenario, a procedure would be faster depending on the size of the return data (which dictates how much data would need to be copied.) But that is likely to be true ONLY in post 8i databases when the pass-by-reference parameter is marked as NOCOPY; if the pass-by-reference parameter is not NOCOPY, Oracle's parameter passing scheme would be copy-in and copy-out even for OUT parameters.

-----Original Message-----
Sent: Tuesday, July 08, 2003 6:04 AM
To: Multiple recipients of list ORACLE-L

Hi All,

If I create a Database Procedure and a Database Function to achieve the same functionality, which one would be faster and why?

TIA Regards
Dhanvir
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Rudy Zung
INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Jul 08 2003 - 16:51:08 CDT