Alright, since I was intrigued by this, I've decided to
take some empirical measurements.
I compare:
1) procedure with out parameter
2) procedure with out nocopy parameter
3) 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).
