I use tMSSqlOutput. In the advanced settings, there is "commit every" and "Use batch size" options. May I know what the difference between these two? Thanks.
Hi,
Commit and Batch size are inter-related to each other.
Lets consider 1000 records to be inserted in a DB.
In this case, a process need to send 1000 INSERT statements each one for 1 row. hence there will be 1000 batches, each batch for 1 row.
Defining the batch size is depend on the data size or row counts to be inserted or need to be processed.
Commit is the process of committing the rows in db within each batch rows together.
batch size can be use when there is a situation insertion of bulk number of rows.
for example, let us consider insertion of 1 million records.
Batch size is 100 and commit size is 500. So for each batch 1000 records will be inserted and for each batch 500 records will be committed.
Hi @karthikj18:
I know this post is rather old, but would you be kind enough to clarify your response?
I understand your explanation of
Batch size. For a JDBC connection, for example, batch size simply determines the number of JDBC
addBatch calls that will be made before
executeBatch is called. This is a crucial parameter as it avoids a per insert/update DB hit, which is a profound performance killer.
But I don't understand your explanation of
Commit every. First, it seems as if it would only apply where
tMSSqlCommit is in use and the connection is not set to auto-commit. In that case, doesn't it specify the number of batches that must accumulate (via
addBatch) before being committed?
Thanks!
