PRO*C Performance of bulk insert

user11986827

I'm looking for advice on how to get the best performance out of large inserts in PRO*C.  Any information is appreciated.

We have a C++ application that stores data in memory via arrays. We use Pro*C to take that C++ array data and perform a bulk insert using the following method:

EXEC SQL INSERT INTO emp (empno, ename, deptno)

    VALUES (:emp_number, :emp_name, :dept_number);

Is this the fastest possible way to insert data into Oracle with Pro*C?

Any suggestions on how to do large inserts in PRO*C faster?

AndrewSayer

user11986827 wrote:I'm looking for advice on how to get the best performance out of large inserts in PRO*C. Any information is appreciated.We have a C++ application that stores data in memory via arrays. We use Pro*C to take that C++ array data and perform a bulk insert using the following method:EXEC SQL INSERT INTO emp (empno, ename, deptno)  VALUES (:emp_number, :emp_name, :dept_number); Is this the fastest possible way to insert data into Oracle with Pro*C? Any suggestions on how to do large inserts in PRO*C faster?

If emp_number etc are arrays then this will be doing bulk array binding to the insert statement which will probably give you the best performance (without considering parallelism or direct path, neither of which should be considered lightly).

https://docs.oracle.com/cd/A97630_01/appdev.920/a97269/pc_08arr.htm#641

John Thorton

user11986827 wrote:I'm looking for advice on how to get the best performance out of large inserts in PRO*C. Any information is appreciated.We have a C++ application that stores data in memory via arrays. We use Pro*C to take that C++ array data and perform a bulk insert using the following method:EXEC SQL INSERT INTO emp (empno, ename, deptno)  VALUES (:emp_number, :emp_name, :dept_number); Is this the fastest possible way to insert data into Oracle with Pro*C? Any suggestions on how to do large inserts in PRO*C faster?

IMO, above is NOT a large insert.

It is a 1 row INSERT repeated many times.

INSERT TRIGGER on EMP will make it slower.

any INDEX on EMP will make it slower.