Forum Stats

  • 3,741,293 Users
  • 2,248,405 Discussions
  • 7,861,728 Comments

Discussions

PRO*C Performance of bulk insert

user11986827
user11986827 Member Posts: 1 Red Ribbon
edited Apr 19, 2018 12:13PM in General Database Discussions

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 Apr 19, 2018 11:58AM
    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 Apr 19, 2018 12:13PM
    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.