When throughput is an issue and you need to update or delete a large number of records, reducing the number of roundtrips to the DBMS may be the answer. See an overview at Blocking Data Updates.
The differences in performance between optimized update/delete, single-record update/delete and block update/delete are quite important. Optimized update/delete usually delivers the best performance, as it only requires one trip to the server. The worst case is usually single-record update/delete, as it requires as many as two roundtrips to the server for every record involved. Block update/delete is usually a good alternative to single-record update/delete if optimization is not possible.
The Blocking clause in a For Each group, as described below, activates update and/or delete blocking in a For Each command. The NumericExpression specifies the number of records in each block
[Order ... ] [Where ... ] [defined by ... ]
/* Delete Code */
/* Update Code */
/* When none code */
Where Att1 < Value1
Att2 = Value2
When the Blocking clause is specified, the code generator will be similar to the following:
Add record to block (Update or delete code)
NumberOfRecordsUpd += 1 or NumberOfRecordsDel += 1 (dependant of Update or Delete code)
If NumberOfRecordsUpd = NumericExpression
Update Record Block
NumberOfRecordsUpd = 0
If NumberOfRecordsDel >= NumericExpression
Delete Record Block
NumberOfRecordsDel = 0
/* Here goes When none */
- If Blocking is specified and no updates and/or deletes are performed, it will be ignored.
- Block size applies to deletes and updates. You cannot specify a different number for each.
- Implementation uses different blocks for updates and deletes. One may be filled before the other. For example, there may be 5 updates and only 1 delete executed. If the Block size is, say, 5, then the update block is sent to the server but the delete block is not.
- Executing a Commit 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 difference in performance.
- Executing a Rollback within the scope of the group causes all pending to be cleared, so they will never be sent to the server.
This clause is not supported by the following platforms:
- Java - Db2 udb
- Net - MySql
- Net - Informix
- Net - Postgresql
- NetCore - Postgresql