Bulk-Load Support

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 or 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.

Note: Using the bulk copy mode with the select into or 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.

This table guides you on what bulk-load option to use.

Bulk-Load Option Usage
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`or `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 or bulkcopy and the conditions under which high-speed or logged bulk copy is used.

EnableBulkLoad Connection Property

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);

Performance Considerations

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.

Limitations

Triggers are ignored on tables selected for bulk loading.