Host Arrays - performance gain

To achieve a better perfORMance in Pro*C/C++ application, using host arrays would be efficient than the usage of the simple host variables during the data insert. This document is about to detail how much runtime savings we gain from using the host arrays when inserting data into an oracle database table.

When using simple host variables to insert data, data inserting is one row based. For example, we have below sql insert statement, and 4,500 rows to be inserted into the table EMP. During the execution, the SQL statement would be executing 4,500 times; one row at a time.

insert into table emp(empno, ename, sal) values(:hv1,:hv2,:hv3);

However, when an array of 4,500 members is declared and the data is stored in the host array, the database would consider a batch of data insert during the runtime, which is the 4,500 rows inserted would be treated as one transaction.

A Pro*C/c++ program is built to run the tests. Some elements of the program are as follows:

A C structure - Host array to hold all 4,500 data rows.

A timer – to calculate the time elapsed.

The program executes on a client workstation, which may test network round trips.

Enough table segments space provided, which does not need table to extent segments during the runtime.

Table EMP:

Name Null? Type

----------------- -------- ------------

EMPNO NOT NULL NUMBER(4)

ENAME VARCHAR2(20)

SAL NUMBER(7,2)

The program total run 7 times, each time, truncate table emp is used. below are the data collected.

 #1:

The total time of the LoopIns program execution = 1.609458

The total time of the ArrayHost program execution = 0.008854

Result: 99.450% saved.

#2:

The total time of the LoopIns program execution = 1.587075

The total time of the ArrayHost program execution = 0.008567

Result: 99.460% saved.

#3:

The total time of the LoopIns program execution = 1.531888

The total time of the ArrayHost program execution = 0.008677

Result: 99.434% saved.

#4:

The total time of the LoopIns program execution = 1.614283

The total time of the ArrayHost program execution = 0.008676

Result: 99.463% saved.

#5:

The total time of the LoopIns program execution = 1.564746

The total time of the ArrayHost program execution = 0.008582

Result: 99.452% saved.

#6:

The total time of the LoopIns program execution = 1.531173

The total time of the ArrayHost program execution = 0.008060

Result: 99.474 saved.

#7:

The total time of the LoopIns program execution = 1.461805

The total time of the ArrayHost program execution = 0.008107

Result: 99.445% saved.

On average, 99.454% runtime savings are achieved by using the host arrays to insert 4,500 rows of data into the database table. Therefore, based on above testing data, using host arrays do provide a better performance gain during the data processing.