Fast Insert

Fast Insert

When inserting rows in a table using JDBC or ODBC, InterSystems IRIS by default automatically performs highly efficient Fast Insert operations. Fast Insert moves the normalization and formatting of the data being insert from the server over to the client. The server can then directly set the whole row of data for a table into the global without manipulation on the server. This offloads these tasks from the server onto the client and can dramatically improve INSERT performance. Because the client is assuming the task of formatting the data, there may be an unforeseen usage increase in your client environment. You can use the FeatureOption property to disable Fast Insert if this is an issue.

Fast Insert must be supported on both the server and the client. To enable or disable Fast Insert in the client, use the FeatureOption property in the definition of the class instance as follows:

Properties p = new Properties();
p.setProperty("FeatureOption","3"); // 2 is fast Insert, 1 is fast Select, 3 is both

If Fast Insert is active, an INSERT executed using a cached query is performed using Fast Insert. This initial INSERT that generated the cached query is not performed using Fast Insert. This enables you to compare the performance of the initial insert with subsequent Fast Inserts executed using the cached query. If Fast Insert is not supported (for any of the following reasons), an ordinary INSERT is performed.

Fast Insert must be performed on a table. It cannot be performed on an updateable view. Fast Insert is not performed when the table has any of the following characteristics:

• The table uses embedded (nested) storage structure (%SerialObject).

• The table is a linked table.

• The table is a child table.

• The table has an explicitly defined multi-field IDKEY index.

• The table has a SERIAL (%Counter), AUTO INCREMENT, or %RowVersion field.

• The table has a property (field) with a defined VALUELIST parameter.

• The table has a defined insert trigger.

• The table performs LogicalToStorage conversion of field values.

• The table is a Shard Master table.

Fast Insert cannot be performed if the INSERT statement has any of the following characteristics:

• It specifies a stream field (data type %Stream.GlobalCharacter or %Stream.GlobalBinary), a collection field (lists or arrays), or a ReadOnly field. These types of fields can exist in the table, but cannot be specified in the INSERT.

• It specifies a literal value enclosed with double parentheses that suppresses literal substitution. For example, (('A')).

• It specifies a {ts } timestamp value that omits the date value.

• It includes a DEFAULT VALUES clause.

For SQL Statement auditing events generated through a database driver, an INSERT statement that uses the Fast Insert interface has a description of SQL fastINSERT Statement. If the Fast Insert interface is used, the Audit event does not include any parameter data, but includes the message Parameter values are not available for a fastInsert statement.