15. Updating Data : Batch and Bulk Updates
 
Share this page                  
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:
Operation
Raw Table
Table with Clustered Index when Empty
Table with Clustered Index when Not Empty
INSERT
Batch by default;
Batch with INSERTMODE ROW;
Bulk with INSERTMODE BULK
Batch by default;
Batch with INSERTMODE ROW;
Bulk with INSERTMODE BULK
Batch
DELETE
Batch
Batch
Batch
UPDATE
Batch
Batch
Batch
COPY FROM
Bulk by default;
Batch with INSERTMODE ROW;
Bulk with INSERTMODE BULK
Bulk by default;
Batch with INSERTMODE ROW;
Bulk with INSERTMODE BULK
Batch
CREATE TABLE AS SELECT
Bulk by default;
Batch with INSERTMODE ROW;
Bulk with INSERTMODE BULK
Not applicable
Not applicable
INSERT...SELECT
Bulk by default;
Batch with INSERTMODE ROW;
Bulk with INSERTMODE BULK
Bulk by default;
Batch with INSERTMODE ROW;
Bulk with INSERTMODE BULK
Batch
Batch INSERT through an ODBC, JDBC, or .NET interface
Bulk by default;
Batch with INSERTMODE ROW;
Bulk with INSERTMODE BULK
Bulk by default;
Batch with INSERTMODE ROW;
Bulk with INSERTMODE BULK
Batch
MERGE...WHEN NOT MATCHED INSERT
Bulk by default;
Batch with INSERTMODE ROW;
Bulk with INSERTMODE BULK
Bulk by default;
Batch with INSERTMODE ROW;
Bulk with INSERTMODE BULK
Batch
vwload or COPY VWLOAD
Bulk (Batch with ‑R)
Bulk (Batch with ‑R)
Batch
Spark SQL through the Spark-Vector Connector
Bulk by default;
Batch with INSERTMODE ROW;
Bulk with INSERTMODE BULK
Bulk by default;
Batch with INSERTMODE ROW;
Bulk with INSERTMODE BULK
Batch
An alternative method of updating data is combining tables (see Combining Tables).