Oracle Procedure with Array param vs fixed param performance

Question

I was wondering the performance impact if I call a procedure with an array of n-number of values vs calling n times a procedure that accepts only one param in parallel.

I am not a DB developer but a DB developer in our team said when you run a procedure with single param run faster than procedure with array param with single element in it. So I tested that theory on a time-consuming SP and that turned out to be true.

What's going on here? Can someone explain? Is it universal or just DATA Model specific result.

Help is much appreciated since we have to improve the performance of out system but nailing down some long running SPs.

Kapil

Answer 1

You probably want to stick with array parameters. The extra overhead of supporting multiple values is irrelevant compared to the benefit of combining operations.

Batching operations is the key to database performance. Performing operations in sets, chunks, bulk, etc., will often be orders of magnitude faster than row-by-row processing. I often see hundreds of JVMs struggle to process megabytes per minute one row-at-a-time. And I often see a single query easily process gigabytes per minute.

There are some exceptions to this. There are some systems where everything is done one-at-a-time. In those cases you're stuck worrying about such trivia. I've been there and it's painful. It leads to ridiculous changes, like replacing functions with procedures because they can be infinitesimally faster in some cases. In that case, start thinking about a complete rewrite.

Passing in multiple values should be much faster. It can reduce the number of procedure calls (less network traffice, query parsing, etc). And it can enable you to use features like bulk collect and FORALL (less parsing, fewer context switches between SQL and PL/SQL, etc).

But if you are worried about the overhead of calling trivial procedures millions of times, then yes, it is several times slower to use arrays. In the example below, using composite data types takes 28 seconds whereas a primitive data type takes 7 seconds.

declare
    v_number number := 1;
    v_numbers sys.odcinumberlist := sys.odcinumberlist(1);
    v_number_out number;

    procedure one_parameter(p_value in number, p_return out number) is
    begin
        p_return := p_value+1;
    end;

    procedure many_parameters(p_value in sys.odcinumberlist, p_return out number) is
        v_number number;
    begin
        p_return := p_value(1)+1;
    end;
begin
    --10M: 7.035
    --10M: 7.004
    for i in 1 .. 100000000 loop
        --Using predefined variables:
        --one_parameter(v_number, v_number_out);              -- 7.0035 seconds
        --many_parameters(v_numbers, v_number_out);           -- 7.004 seconds

        --Passing in values:
        --one_parameter(1, v_number_out);                     -- 7.161 seconds
        many_parameters(sys.odcinumberlist(1), v_number_out); -- 27.877 seconds
    end loop;

    --Make sure the number is used so it won't be optimized away.
    dbms_output.put_line(v_number_out);
end;
/