Hi,
I have been doing some reading regarding optimizing my PLSQL code. One of the most common improvements I have read is to use BULK COLLECT INTO rather than use a FOR LOOP because of the reduction in the number of context switches between the PLSQL and SQL engines. I have used FOR loops a lot in my code mainly because of their readability and its a habit that has stuck.
From my testing, I am not seeing any performance improvemnets. I have run the test below against a dataset with 425,000 rows.
I have created the a PLSQL table and run a BULK COLLECT INTO test against it.
PROCEDURE bulk_collect (p_dept_in xxmel_pwa_salaries.department_id%TYPE
,p_message_out OUT VARCHAR2) --IT = 11
IS
TYPE employee_tt IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
l_part_no employee_tt;
l_comments employee_tt;
CURSOR cur IS
select part_no
, comments
--, transaction_date
--, transacted_qty
from xxmel_vmi_material_txns
where transaction_date > '01-JAN-2013'
and part_no is not null;
l_start NUMBER;
l_end NUMBER;
BEGIN
l_start := dbms_utility.get_time;
FOR x IN cur
LOOP
NULL;
END LOOP;
l_end := dbms_utility.get_time;
dbms_output.put_line('Time for standard fetch '||(l_end - l_start) ||' hsecs' );
l_start := dbms_utility.get_time;
OPEN cur;
LOOP
EXIT WHEN cur%NOTFOUND;
FETCH cur BULK COLLECT INTO l_part_no, l_comments;
END LOOP;
CLOSE cur;
l_end := dbms_utility.get_time;
dbms_output.put_line('Time for BULK fetch '||(l_end - l_start) ||' hsecs' );
END bulk_collect;
DECLARE
x_out VARCHAR2(1000);
BEGIN
xxmel_chris_testing.bulk_collect(11,x_out);
dbms_output.put_line (x_out);
END;
Output:
Time for standard fetch 82 hsecs
Time for BULK fetch 104 hsecs
As you can see from my test, I am experiencing the BULK COLLECT slower than a normal FOR LOOP.
Can anyone explain why this might be the case here? The table is quite large and is indexed.
Thanks
Chris