Host Arrays，
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.