EarthSoft.Common.Data.Connection.BatchInsert

As described in the EDP Commit techniques article, BatchInsert is one of the techniques that may be used when committing records to the database. This BatchInsert configuration setting allows users to configure if and how the BatchInsert process operates. If BatchInsert is disabled (or fails), then EDP will revert to row-by-row processing.

The BatchInsert ST_CONFIG setting, as described below, is optional. If the setting does not exist, the BatchInsert technique will not be used. If this setting exists (and the value is greater than or equal to zero – see below in table), BatchInsert will be used. This setting is independent of any particular EDD format; when configured, it will be applied to all formats (as well as loading data through Enterprise EDP or manually in Professional).

The primary performance improvement (over the row-by-row technique) happens because multiple records are sent to the database in a single batch, thus reducing the network overhead. Optimal performance is based on optimal batch size—big enough to minimize network traffic but small enough to not exceed the database connection timeout. When enabled with a value of “0” (zero – see below in table), BatchInsert will optimize batch size by progressively increasing the batch size after each batch until the execution time for the batch approaches the connection timeout. For most situations, this default functionality of progressive batch size is recommended.

EQuIS Version: v7.20.2+

Products Affected: Enterprise EDP, Professional EDP, Professional

Requires Module: EDP

ST_CONFIG	VALUE	REMARK	VALID VALUES
CONFIG_SECTION	EarthSoft.Common.Data.Connection	Required
CONFIG_KEY	BatchInsert	Required
OBJECT_TYPE	’*’ or a specific table name	Required	Asterisk will apply to all tables; specific table name will apply to just that specific table.
OBJECT_VALUE	-1 or 0 or X (positive integer)	Required	-1 = Disable BatchInsert. 0 = Use BatchInsert with progressive batch size. X = Use BatchInsert with batch size of X records.
STRING_VALUE		Not applicable

Example: The following ST_CONFIG record enable BatchInsert for all tables with a progressive batch size.

CONFIG_SECTION	CONFIG_KEY	OBJECT_TYPE	OBJECT_VALUE
EarthSoft.Common.Data.Connection	BatchInsert	*	0

Example: The following ST_CONFIG record will enable BatchInsert for only the DT_RESULT table and force a batch size of 2500 records.

CONFIG_SECTION	CONFIG_KEY	OBJECT_TYPE	OBJECT_VALUE
EarthSoft.Common.Data.Connection	BatchInsert	DT_RESULT	2500

Remark: You may have multiple instances of this config setting for different tables.