When throughput is an issue and you need to insert a large number of records, reducing the number of roundtrips to the DBMS may be the answer. See an overview on Blocking Data Updates.
When large numbers of records have to be inserted, performance differs significantly if you insert records in blocks instead of inserting them one by one (read block insert performance). These significant differences in performance depend on the number of rows and the block size.
The New syntax supports the 'Blocking NumericExpression' optional clause as shown below. Its presence activates the Block (Batch) insertion mechanism and controls the number of records per block. See also the complete syntax of the New command.
New [Blocking NumericExpression] ....
[When Duplicate] .... EndNew
new blocking 100
Att1 = ...
Att2 = ...
Att2 = ...
The real insertion will be performed at the end of each block. Suppose the New command has a blocking factor N and is inside a repetitive structure (a For Each command, for example). Every time an execution of the body of the For Each command (and of the New command inside it) is performed, the New record is not actually inserted, but is added into a size N memory block (a buffer). After that, if the buffer is filled, a special insert (of many rows) is sent to the database, in order to insert the whole block. Then, if some of the N records to be added are found to be duplicated, the special insertion will fail, and a one-by-one insertion will be performed, running through the N block, using the simple insert command, as you can see below.
When block saving is activated, the code generated for the group would be similar to the following:
Add record to block
NumberOfRecords += 1
if NumberOfRecords >= NumericExpression
Insert Record Block //masive insertion command
When Duplicate //some record insertion has failed
/* Scan every record in block for duplicates */
For each record in block
Insert Record //simple insertion command
/* Here goes When duplicate user code */
NumberOfRecords = 0
Note that in the event of a duplicate key error, the code sweeps the block's records attempting to insert them one by one and executing the When Duplicate code for the records that generate duplicate keys. When a Rollback command is executed, the transaction's Rollback is executed, deleting all the records in the block (buffer) that are ready to be inserted.
- Is not supported in the following DBMS: Informix, Mysql and Postgres.
It depends on the logic, the frequency of When Duplicate codes, etc. If you need to access the record immediately after inserting it, you cannot use Block Insert. If there are many duplicate records it is usually best not to use it.
Different numbers of records per block need to be tried for each case. There is no set recipe.
When a Commit command is performed, whether it is implicit or explicit.
If NumericExpression is a variable expression, its value will be considered only at the first insert of each block.
The When Duplicate code is executed up to the Return. It does not go on with the rest of the program (the When Duplicate code is generated in a new routine that is called when the buffer of a cursor batch is completed or when a Commit command is performed).
The corresponding call is made in both cases.
Executing a Commit command within the scope of the group causes all pending updates/deletes to be sent to the server. That is: do not issue a Commit command for every record processed as Blocking will not make any performance difference.
When a Rollback command is executed, the transaction's Rollback is executed, deleting all the records in the block (buffer) that are ready to be inserted.