topic Re: SqlOutput advanced settings in Talend Studio

SqlOutput advanced settings

Anonymous — Sat, 16 Nov 2024 12:38:51 GMT

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.

Re: SqlOutput advanced settings

karthikj18 — Wed, 11 Jan 2012 09:20:16 GMT

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.

Re: SqlOutput advanced settings

Anonymous — Thu, 03 Oct 2013 13:58:05 GMT

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!