Performance impact of tMSSqlOutput Commit Every and Batch Size

Anonymous — Thu, 22 Mar 2018 18:02:59 GMT

Bottom-line: For the tMSSqlOutput component, the default "Commit every" and "Batch Size" of 10k provide good performance.

I had reason to investigate performance given some issues I was experiencing. I created a simple job that read from one table and inserted the data into another table in the same database. The table contains 463,122 rows with a single, varchar(50) primary clustered key, 50 columns, and an average record size of 1k bytes. The database was idle with no operations other than my testing. The job uses the "Open source JTDS" provider, with each component having its own connection (built-in) and no "Field Options".

I tried several combinations of sizes for "Commit" and "Batch" to see how they impact performance. This was not a scientific study where I ran hundreds of tests and reconciled the results. This was a seat-of-the-pants look at general behavior. The tests did not account for network or other potential through-put issues.

Based upon the results, I concluded that "Batch" is the number of rows to be "batched up" before sending them to the database and "Commit" is the number of rows to be sent to the database before sending a commit. (For those coming from Datastage, these appear comparable to "Array size" and "Transaction size".)

For this testing configuration, I found that "Batch" size should be definitely enabled (horrendous performance without it) and should be kept the same size as "Commit". Setting "Batch" smaller than "Commit" significantly reduced performance compared to when the same as "Commit". Setting "Batch" larger than "Commit" also reduced performance though not as much.

Re: Performance impact of tMSSqlOutput Commit Every and Batch Size

Anonymous — Tue, 27 Mar 2018 08:40:55 GMT

Hello,

Thanks for sharing your best practices on forum.

Best regards

Sabrina

Re: Performance impact of tMSSqlOutput Commit Every and Batch Size

aashish_21nov — Wed, 13 Jun 2018 09:40:40 GMT

use commit after the insertion process complete. this strategy will save huge amount of time.

Re: Performance impact of tMSSqlOutput Commit Every and Batch Size

Abilaash — Fri, 08 Dec 2023 11:49:56 GMT

Hello @Rod Barnes,

That's a very useful analysis. I have a view in IBM DB2 consisting of 4 million records which is loaded to sql server daily. What will be the recommended commit and batch I should use. Thanks

topic Performance impact of tMSSqlOutput Commit Every and Batch Size in Talend Studio

Performance impact of tMSSqlOutput Commit Every and Batch Size

Re: Performance impact of tMSSqlOutput Commit Every and Batch Size

Re: Performance impact of tMSSqlOutput Commit Every and Batch Size

Re: Performance impact of tMSSqlOutput Commit Every and Batch Size