The Adaptive Server ODBC Driver supports bulk-load interface for fast insertions of large sets of rows to Adaptive Server. This interface is invoked when SQLBulkOperations is used with the SQL_ADD option and the EnableBulkLoad connection property is set. Two types of bulk loading are supported:
Array Inserts – you can use this type of bulk-loading within a single or multistatement transaction; the database connection can be set to autocommit off.
Bulk Copy – this is supported only in single statement transactions, and you must to ensure that:
The database connection is set to autocommit on.
The select into/bulkcopy option on Adaptive Server is turned on.
If the target table meets the criteria for high-speed version of bulk copy, Adaptive Server inserts the rows using this version of bulk copy.
Using the bulk copy mode with the select into/bulkcopy option enabled
affects the recoverability of the database. After the bulk
copy operation is complete, the system administrator must
dump the database to ensure its future recoverability.
The following table guides you on what bulk-load option to use.
Use case |
Additional consideration |
Bulk-load option to use |
Note |
|---|---|---|---|
Insertion of single or small number of rows. |
None |
||
Insertion of large batch of rows. |
The batch is part of a multistatement transaction. |
Array Inserts |
Rows are inserted faster than when bulk load is disabled. |
You cannot enable the Adaptive Server select into or bulkcopy option because of recoverability considerations. |
Array Inserts |
Rows are inserted faster than when bulk load is disabled. |
|
The batch is a single transaction and the Adaptive Server select into/bulkcopy option is enabled. |
Bulk Copy |
Adaptive Server can use high-speed bulk copy, which is faster than array inserts. The performance of Bulk Copy is still slightly faster than Array Inserts even if high-speed bulk copy is not used. |
See the Adaptive Server Enterprise Utility Guide for information about the implications of enabling select into/bulkcopy and the conditions under which high-speed or logged bulk copy is used.
Enable or disable bulk-load support using the EnableBulkLoad connection property:
0 – the default value, which disables bulk load.
1 – enables bulk load using array insert.
2 – enables bulk load using the bulk copy interface.
3 – enables bulk load using the fast logged bulk copy interface.
Alternatively, use the Sybase-specific SQL_ATTR_ENABLE_BULK_LOAD connection attribute to set EnableBulkLoad programmatically. The attribute accepts the same values as EnableBulkLoad. For example:
sr = SQLSetConnectAttr(hdbc, SQL_ATTR_ENABLE_BULK_LOAD, (SQLPOINTER)3, SQL_IS_INTEGER);
Although this feature does not require special configuration on the server, a larger page size and network packet size significantly improves performance. Depending on the client memory, using larger batches also improves performance.
Triggers are ignored on tables selected for bulk loading.
Enabling bulk load using the ODBC Data Source Administrator user interface
Open the Data Source Name (DSN) Configure window from the ODBC Data Source Administrator.
Select the Advanced tab.
Select the appropriate option under “Enable Bulk Load.”
The default value of EnableBulkLoad connection property is 0, which means insert commands are used.
Enabling bulk load using the ODBC connection string
Use SQLDriverConnect to specify a connection string.
Set the EnableBulkLoad connection string property to 0, 1, 2, or 3, as appropriate. For example:
Driver=AdaptiveServerEnterprise;server=server1; port=port1;UID=sa;PWD=;EnableBulkLoad=1;
