Reduce database calls by posting Multiple Records from Application to Database

One of my friend who was working in a log processing kind of application, which was posting one record at a time to database, asked me a suggestion on how to post multiple records in batch for inserting in Oracle Database to gain performance by reducing the database calls. With this post I am sharing the simplest solution which I shared with during that chat over lunch.

Solution was pretty simple:
1) Create a schema level table type object for the table
2) Create a procedure with collection parameter of that table type object
3) Use Table operator to insert data in table from collection (parameter)
4) Use that procedure at application to reduce the database calls

Demonstration of above:

-- Table for which we want batch insert from application

SQL> create table log_store
  2  (
  3     log_id number(10) primary key,
  4     log_date date,
  5     log_msg varchar2(4000)
  6  );
Table created.

-- Object Type of same structure as table

SQL> create type log_store_type as object
  2  (
  3     log_id number(10),
  4     log_date date,
  5     log_msg varchar2(4000)
  6  );
  7  /
Type created.

-- Schema level table type object for the table

SQL> create type log_store_tbl as table of log_store_type;
  2  /
Type created.

-- Procedure to take table type object as parameter
-- and batch insert the collection

SQL> create or replace procedure pro_log_store (p_log_store log_store_tbl)
  2  as
  3  begin
  4    insert into log_store (log_id, log_date, log_msg)
  5    select log_id, log_date, log_msg
  6      from table(p_log_store);
  7  end;
  8  /
Procedure created.

Now we are ready. We simply need to call "pro_log_store" to batch insert the complete collection in database from our application or any client as

SQL> begin
  2     pro_log_store (
  3             log_store_tbl(
  4                     log_store_type(1, sysdate, 'Log Message 1'),
  5                     log_store_type(2, sysdate, 'Log Message 2'),
  6                     log_store_type(3, sysdate, 'Log Message 3'),
  7                     log_store_type(4, sysdate, 'Log Message 4'),
  8                     log_store_type(5, sysdate, 'Log Message 5')));
  9     commit;
 10  end;
 11  /
PL/SQL procedure successfully completed. 
  
SQL> select * from log_store;

    LOG_ID LOG_DATE  LOG_MSG
---------- --------- ----------------------------
         1 16-APR-16 Log Message 1
         2 16-APR-16 Log Message 2
         3 16-APR-16 Log Message 3
         4 16-APR-16 Log Message 4
         5 16-APR-16 Log Message 5

As we can see, by above approach we can pass multiple records from the application to database and can significantly improve performance by reducing the database calls. Bind parameters could be a deciding factor for collection size.

Feedback are always well appreciated. Please do add you comments.

Related Posts:
- SQL Tuning: Partition Pruning and HASH JOIN - Real Life Example
- Why my distinct query is not using the Index?
- Avoiding unnecessary function calls to optimize SQL statements
- PLSQL Tuning: Bind Variables and execute immediate
- DBMS_PROFILER: How to analyze pl/sql performance
- Query optimization tips for Oracle