If Batch journal inserts is selected, the journal table inserts are executed in a batch instead of one row at time. For each journal target table using the batch option there is a batch statement associated with the table. Each batch uses a prepared statement, which requires only the values to be added to the statement. When the rows are received from the source, they are added to the batch for the table specified in the insert instead of immediately executing the insert. When enough rows have been added to the batch to reach the batch threshold of 500 rows, the batch is executed. When the commit row for the transaction is received from the source, each batch with pending inserts is executed and after all pending inserts are complete, the transaction is committed.
Using prepared batch statements provides a significant performance improvement. However, since the rows for each table are added to the batch, instead of immediately being applied, the actual updates to the target tables can be executed in a different order than when they were executed on the source. The order of updates within the same table is the same on the target as on the source. The order of updates between multiple tables may be different. The following example shows how the apply order can be changed:
Source Server execution order:
-
Insert 5 rows into TABLEA.
-
Insert 5 rows into TABLEB.
-
Insert 500 rows into TABLEC.
Target Server apply order:
-
Insert 500 rows into TABLEC (because batch threshold was reached.)
-
Insert 5 rows into TABLEB.
-
Insert 5 rows into TABLEA.
If there are any dependencies between tables, such as Primary Key/Foreign key referential integrity constraints, changing the apply order will cause a problem. In the above example, if TABLEA has a primary key and TABLEC has a foreign key relationship, an error will occur when inserting into TABLEC because the rows must be inserted into TABLEA first. The batch option should not be selected if there are order dependencies between tables.