Friday, 18 August 2017
General

How fast is PL/SQL Bulk processing?

PL/SQL BULK Processing allows to batch or group number of records and sends in batches to a database for processing.

PL/SQL is procedural language extension for SQL. Each command is executed in sequence. PL/SQL code is executed by PL/SQL Engine while SQL by SQL Engine. It is efficiently managed by Oracle Database internally so that you never notice any lag in execution.

PLSQL and SQL Architecture
Image Courtesy – Oracle

What if you are dealing with millions of records?

There would be millions switch between two engine and can add significant execution time, isn’t it?

In this article, let’s see how fast and efficient is bulk processing.



Do you really get the benefit of using bulk processing? We will compare run time of sequential and bulk method.

Let’s create a dummy table which is an exact copy of DBA_OBJECTS. Use below script

CREATE TABLE XXDBA_OBJECTS as SELECT * FROM DBA_OBJECTS WHERE 1=2;

Total Record Count in DBA_OBJECTS:- 353426

Below are some examples. You can use DBMS_UTILITY.get_cpu_time to print time before and after calling the procedure. Set timing on also print the execution time.

EXAMPLES

Example 1 – Direct Insert command

SET timing ON;
BEGIN
INSERT INTO XXDBA_OBJECTS
SELECT * FROM DBA_OBJECTS;
END;
Elapsed: 00:00:50.213

Example 2 – Insert using BULK Collect and BULK Insert

SET timing ON;
DECLARE
TYPE l_tbl_objects
IS
TABLE OF DBA_OBJECTS%ROWTYPE INDEX BY BINARY_INTEGER;
l_objects l_tbl_objects;
BEGIN
SELECT db.* BULK COLLECT INTO l_objects
FROM DBA_OBJECTS db;
FORALL i IN 1..l_objects.count
INSERT INTO XXDBA_OBJECTS VALUES
l_objects(i)
;
END;
PL/SQL procedure successfully completed.
Elapsed: 00:00:56.231

Example 3 – Insert in Normal for Loop

SET timing ON;
DECLARE
CURSOR get_data
IS
SELECT db.* FROM DBA_OBJECTS db;
BEGIN
FOR rec_data IN get_data
LOOP
INSERT INTO XXDBA_OBJECTS VALUES rec_data;
END LOOP;
END;
PL/SQL procedure successfully completed.
Elapsed: 00:01:08.598
Below table shows data with descreasing run time.
RUN TIME DETAILS
Method Runtime
Direct Insert command 00:00:50.213
Insert using BULK Collect and BULK Insert 00:00:56.231
Insert in Normal for Loop 00:01:08.598

Above result clearly, show the difference between normal and bulk processing. BULK Processing really efficient. You should always use below approach in sequence when dealing with huge records.

  • Direct insert
  • Bulk processing
  • Normal for loop

 

Leave a Reply