There is almost never a performance difference between procedures and functions.
In a few extremely rare cases:
- A procedure
IN OUT argument is faster than a function return, when inlining is enabled.
- A procedure
IN OUT argument is slower than a function return, when inlining is disabled.
Test code
--Run one of these to set optimization level:
--alter session set plsql_optimize_level=0;
--alter session set plsql_optimize_level=1;
--alter session set plsql_optimize_level=2;
--alter session set plsql_optimize_level=3;
--Run this to compare times. Move the comment to enable the procedure or the function.
declare
v_result varchar2(4000);
procedure test_procedure(p_result in out varchar2) is
begin
p_result := '0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789';
end;
function test_function return varchar2 is
begin
return '0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789';
end;
begin
for i in 1 .. 10000000 loop
--Comment out one of these lines to change the test.
--test_procedure(v_result);
v_result := test_function;
end loop;
end;
/
Results
Inlining enabled: PLSQL_OPTIMIZE_LEVEL = 2 (default) or 3
Function run time in seconds: 2.839, 2.933, 2.979
Procedure run time in seconds: 1.685, 1.700, 1.762
Inlining disabled: PLSQL_OPTIMIZE_LEVEL = 0 or 1
Function run time in seconds: 5.164, 4.967, 5.632
Procedure run time in seconds: 6.1, 6.006, 6.037
The above code is trivial and perhaps subject to other optimizations. But I have seen similar results with production code.
Why the difference doesn't matter
Don't look at the above test and think "a procedure runs twice as fast as a function!". Yes, the overhead of a function is almost twice as much as the overhead of a procedure. But either way, the overhead is irrelevantly small.
The key to database performance is to do as much work as possible in SQL statements, in batches. If a program calls a function or procedure ten million times per second then that program has serious design problems.
