Batch and Bulk Updates
Updating data in Vector tables can be done as either a batch or bulk operation.
Batch operations go to the PDTs (memory) by default. Bulk operations are written to the table files on disk by default.
Bulk operations have higher performance and lower memory footprint (data goes directly to disk), but concurrent transactions with bulk operations on the same table are not allowed. The later transaction to commit will conflict and need to be rolled back. When concurrency is needed, you can use SET INSERTMODE ROW to make the operations that by default execute in bulk mode execute in batch mode. See
Allowing Concurrent Inserts.
By default, SQL statements and the vwload command map to batch and bulk DML operations as follows:
• INSERT is a batch operation that quickly adds a small number of records.
• DELETE is a batch operation that quickly deletes a small number of records.
• UPDATE is a batch operation that quickly modifies a small number of records.
• CREATE TABLE AS SELECT is a bulk operation that adds a large number of records.
• INSERT...SELECT is a bulk operation that adds a large number of records.
INSERT...SELECT is a batch operation if executed on a non-empty table with a clustered index.
• A batch INSERT through an ODBC, JDBC, or .NET based application is a bulk operation that adds a large number of records.
A batch INSERT is a batch operation if executed on a non-empty table with a clustered index.
• COPY FROM is a bulk operation that adds a large number of records.
COPY FROM is a batch operation if executed on a non-empty table with a clustered index.
• vwload is a bulk operation that adds a large number of records.
vwload is a batch operation if executed on a non-empty indexed table.
vwload is a batch operation if --rowmode (-R) parameter is used.
In scenarios where both batch and bulk updates are valid, you can choose the method of operation by specifying:
• SET INSERTMODE BULK for bulk
• SET INSERTMODE ROW for batch
COPY FROM, INSERT...SELECT, and vwload are batch operations if executed on a non-empty table with a clustered index. For these operations to be bulk, the target table with the clustered index must meet all these conditions:
1. The table is empty.
2. There have been no batch updates performed on this table since the last MODIFY...TO COMBINE call for the table.
Note: The table can be seen as empty, but there can still be batch updates stored for it in memory. This can happen if a user issues, for example, an INSERT or COPY command followed by a DELETE.
3. If the index is based on a foreign key, there can be no batch updates on the referenced table.
The following table summarizes the various update methods and their behavior depending on the target table:
An alternative method of updating data is
combining tables (see
Combining Tables).
Last modified date: 11/09/2022