Bulk-Load Support in Adaptive Server ADO.NET Data Provider

Setting the ENABLEBULKLOAD connection property allows ASEBulkCopy to invoke the bulk-load interface. Two types of bulk loading are supported:

Array Inserts – use this type of bulk-loading within a single or multistatement transaction.
Bulk Copy – this is supported only in single-statement transactions, and you must ensure that 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.

Note: 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.

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

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.

Supported ASEBulkCopy Options

ASEBulkCopy Options	Supported Bulk-load Mode
Default	Array Inserts, Bulk Copy, Off
KeepIdentity	Array Inserts, Bulk Copy, Off
KeepNulls	Array Inserts, Bulk Copy, Off
UseInternalTransaction	Array Inserts, Bulk Copy, Off
CheckConstraints	Off
FireTriggers	Off
TableLock	Not supported

Limitations

Computed and encrypted columns are not supported. Also, triggers are ignored on tables selected for bulk-loading.
The CheckConstraints, FireTriggers, and TableLock AseBulkCopy options are supported only as default values; these values are not supported when bulk-loading is disabled.