Using the Bulk Insert Feature

By buffering rows to be inserted, some databases can get a considerable performance boost. Application Engine offers this nonstandard SQL enhancement for the following databases: Oracle, Microsoft SQLServer, and DB2. This feature is named Bulk Insert. For those database platforms that do not support the Bulk Insert feature, this flag is ignored.

You should consider using this feature only when an Insert SQL statement is called multiple times in the absence of intervening Commit statements.

Application Engine ignores the Bulk Insert setting in the following situations:

  • The SQL is not an Insert statement.

  • The SQL is other than an Insert/Values statement that inserts one row at a time.

    For instance, the following statements are ignored: Insert/Select, Update, or Delete.

  • The SQL does not have a Values clause.

  • The SQL does not have a field list before the Values clause.

Note: Application Engine also ignores the Bulk Insert feature when all three of the following conditions are true: the database platform is Oracle, the record contains an EFFDT (effective date) field, and the record contains a mobile trigger. A mobile trigger is required because an Oracle database does not allow the reading of mutating tables in a row trigger.

When the Bulk Insert setting is ignored, Application Engine still runs the SQL; it just does not take advantage of the performance boost associated with the feature.

To prepare or flush a Bulk Insert statement because one of the static binds has changed and the SQL has to reflect that, use %ClearCursor. A flush occurs automatically before each commit.