I'm putting together a package to perform some operations and return an associative array of date spans. I had been using functions, but I started to wonder if that was the best option performance-wise, so I put together a test package to check it out:
CREATE OR REPLACE PACKAGE pk_perf_test IS
TYPE pt_rDateSpan IS RECORD
(
start_date DATE,
end_date DATE
);
TYPE pt_aaDateTable IS TABLE OF pt_rDateSpan INDEX BY PLS_INTEGER;
FUNCTION pf_getDateTable (p_nInputParam INTEGER) RETURN pt_aaDateTable;
PROCEDURE pp_getDateTable (p_nInputParam INTEGER, op_aaDateTable OUT pt_aaDateTable);
PROCEDURE pp_getDateTable_NoCopy (p_nInputParam INTEGER, op_aaDateTable OUT NOCOPY pt_aaDateTable);
END pk_perf_test;
Each of the function/procedures just run the same query with BULK COLLECT into the collection using the input parameter as a bind variable. Like this:
CREATE OR REPLACE PACKAGE BODY pk_perf_test IS
FUNCTION pf_getDateTable
(p_nInputParam INTEGER)
RETURN pt_aaDateTable
IS
l_aaToRet pt_aaDateTable;
BEGIN
SELECT mt.start_date, mt.end_date
BULK COLLECT INTO l_aaToRet
FROM my_table mt
INNER JOIN my_other_table mot ON mt.pk_col = mot.fk_col
WHERE mt.pk_col = p_nInputParam;
RETURN l_aaToRet;
END pf_getDateTable;
PROCEDURE pp_getDateTable
(p_nInputParam INTEGER, op_aaDateTable OUT pt_aaDateTable)
IS
BEGIN
SELECT mt.start_date, mt.end_date
BULK COLLECT INTO op_aaDateTable
FROM my_table mt
INNER JOIN my_other_table mot ON mt.pk_col = mot.fk_col
WHERE mt.pk_col = p_nInputParam;
END pp_getDateTable;
PROCEDURE pp_getDateTable_NoCopy
(p_nInputParam INTEGER, op_aaDateTable OUT NOCOPY pt_aaDateTable)
IS
BEGIN
SELECT mt.start_date, mt.end_date
BULK COLLECT INTO op_aaDateTable
FROM my_table mt
INNER JOIN my_other_table mot ON mt.pk_col = mot.fk_col
WHERE mt.pk_col = p_nInputParam;
END pp_getDateTable_NoCopy;
END pk_perf_test;
I then wrote a short script to test these to determine performance:
DECLARE
l_tsStartTS TIMESTAMP WITH LOCAL TIME ZONE;
l_iFunctionTotal INTERVAL DAY TO SECOND := INTERVAL '0' SECOND;
l_iProcedureTotal INTERVAL DAY TO SECOND := INTERVAL '0' SECOND;
l_iNoCopyTotal INTERVAL DAY TO SECOND := INTERVAL '0' SECOND;
l_aaDummy pk_perf_test.pt_aaDateTable;
l_nDummy INTEGER := 123;
l_nCaseCount INTEGER := 0;
CURSOR c_ids IS
SELECT mt.pk_col
FROM my_table SAMPLE (10) mt;
BEGIN
--Priming Query Run to make sure plan is cached
SELECT mt.start_date, mt.end_date
BULK COLLECT INTO l_aaDummy
FROM my_table mt
INNER JOIN my_other_table mot ON mt.pk_col = mot.fk_col
WHERE mt.pk_col = l_nDummy;
FOR r IN c_ids LOOP
l_nCaseCount := l_nCaseCount + 1;
l_aaDummy.DELETE;
l_tsStartTS := SYSTIMESTAMP;
l_aaDummy := pk_perf_test.pf_getDateTable(r.pk_col);
l_iFunctionTotal := l_iFunctionTotal + (SYSTIMESTAMP-l_tsStartTS);
l_aaDummy.DELETE;
l_tsStartTS := SYSTIMESTAMP;
pk_perf_test.pp_getDateTable(r.pk_col, l_aaDummy);
l_iProcedureTotal := l_iProcedureTotal + (SYSTIMESTAMP-l_tsStartTS);
l_aaDummy.DELETE;
l_tsStartTS := SYSTIMESTAMP;
pk_perf_test.pp_getDateTable_NoCopy(r.pk_col, l_aaDummy);
l_iNoCopyTotal := l_iNoCopyTotal + (SYSTIMESTAMP-l_tsStartTS);
END LOOP;
dbms_output.put_line('Total Cases: '||l_nCaseCount);
dbms_output.put_line('Function Total: '||l_iFunctionTotal);
dbms_output.put_line('Procedure Total: '||l_iProcedureTotal);
dbms_output.put_line('No Copy Total: '||l_iNoCopyTotal);
END;
/
To which I received the following results:
Total Cases: 10787 Function Total: +00 00:00:22.857400 Procedure Total: +00 00:00:04.346413 No Copy Total: +00 00:00:01.942333
Now for my question. I understand why NOCOPY is faster than the normal procedure. What I don't understand is why is the regular procedure so much faster than the function? Thinking it was a fluke, I ran it on multiple environments (multiple times) and received similar results (The result I posted is actually the average of all the runs). Can anyone explain why the performance would be so different? Or point out a flaw in my test?
