Forum Stats

  • 3,732,979 Users
  • 2,246,665 Discussions
  • 7,856,452 Comments

Discussions

PRO*C Performance of bulk insert

user11986827
user11986827 Member Posts: 1 Red Ribbon

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?

Tagged:
Hemant K Chitale

Answers

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited April 2018
    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

    Hemant K Chitale
  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited April 2018
    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.

This discussion has been closed.