Bulk-load support in Adaptive Server ADO.NET Data Provider

Adaptive Server ADO.NET Data Provider supports bulk-load interface for fast insertions of large sets of rows to Adaptive Server. 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.

  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.

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 – off mode, the default value.

• 1 – enables bulk-load using array insert.

• 2 – enables bulk-load using the bulk copy interface.

1. Use SQLDriverConnect to specify a connection string.

2. Set the ENABLEBULKLOAD connection string property to 0, 1, or 2, as appropriate. For example:

   Data Source=server1;port=port1;UID=sa;PWD=;
   Driver=AdaptiveServerEnterprise;
   ENABLEBULKLOAD=1;
   

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.

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