Increasing speed of batch inserts and updates

SQL Guide : Tuning performance with SQL : Optimizing batch inserts and updates : Increasing speed of batch inserts and updates

You can optimize the speed for large batch inserts and updates to solidDB^®. Following are guidelines for increasing speed:

1 Check that you are running the application with the AUTOCOMMIT mode set off.

solidDB^® ODBC Driver’s default setting is AUTOCOMMIT. This is the standard setting according to the ODBC specification. To set your application with AUTOCOMMIT off, call the SQLSetConnectOption function as in the following example:

rc = SQLSetConnectOption
(hdbc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF);

2 Do not use large transactions. Five hundred (500) rows is recommended as the initial transaction size. The optimal value for the transaction size is dependent on the particular application; you may need to experiment.

3 To make batch inserts faster, you can turn logging off. This, however, increases the risk of data loss during system failure. In some environments, this trade-off is tolerable.

Number 1 and 2 of these guidelines are the most important actions you can take to increase the speed of batch inserts. The actual rate of insertions also depends on your hardware, on the amount of data per row, and on the existing indices for the table.