topic Re: Job Performance Optimization in Talend Studio

Job Performance Optimization

abhi90 — Wed, 16 May 2018 08:54:01 GMT

Hi All,

I have designed the Job in the format attached.I am doing Data Conversion of All the Columns from String to Respective target One's and doing ERROR CHECK. I have checked USECURSOR in toracleInPut Component and in Both toracleoutput I have used Batch as 500000 which is much more than my expected Data to be loaded. What I am seeing is My Data getting fetched in 5.32 Seconds but It is taking Time then to commit or ending the Job. I am using both OracleOutput Component using "USE EXISTING CONNECTION " using my Connection established in my Job. WithOut using toracleCommit getting Throughput 1108Rows/sec and after using tOracleCommit getting as 1233Rows/Sec Now. Any way to make it more faster?

@rhall, @manodwhb, @vboppudi, @cterenzi, @vapukov, @shong, @xdshi

Re: Job Performance Optimization

manodwhb — Wed, 16 May 2018 08:58:33 GMT

@abhi90,can you check with tOracleOutputBulkExec,if there is a Target bottelneck?

Re: Job Performance Optimization

abhi90 — Wed, 16 May 2018 09:30:29 GMT

Hi @manodwhb,

toraclebulkexec is for loading from a Data File. In my case tmap is must as I have to do error validation. on some columns incoming Data Flow Value. I Have attached snapshots How I am doing that.

ERROR_CHECK_CRITEO.JPG
Validation_CRITEO.JPG

Re: Job Performance Optimization

Anonymous — Wed, 16 May 2018 09:55:44 GMT

Is there any reason you are using a shared connection? Try setting your output component to use its own connection and handle its own committing. Also, your batch size seems a little high. Scale that back to something between 1000 and 10000.

You should remember that the databases is doing significantly more when inserting data. I think you should be able to get much faster than 1000 rows a second, but you will never get the same speed as you do with the read.

Re: Job Performance Optimization

abhi90 — Wed, 16 May 2018 10:07:33 GMT

Hi @rhall,

Are you referring to use tOracleOutput to use Connection type as Repository?If I have 32770 rows Then what should be my Value in the Settings of toracleOutput "COMMIT_EVERY"? If I am not using Lookups Then what should be my Buffer Size in tMap?I have currently kept it as 2400000. Should This improve my Job Performance?

Re: Job Performance Optimization

abhi90 — Wed, 16 May 2018 10:08:19 GMT

@TRF

Re: Job Performance Optimization

Anonymous — Wed, 16 May 2018 11:42:31 GMT

I do not "know" these things, I experiment with them. They change per query, per environment, etc. Otherwise Talend would just hard code what they believe to be the optimized values.

You should untick "Use an existing connection"in your tOracleOutput component and configure the connection there.

Re: Job Performance Optimization

abhi90 — Thu, 17 May 2018 12:42:00 GMT

Hi @shong, @xdshi,

Please let me know if U guys can help me in optimizing the performance of the Job. I will send my job in that case.

Re: Job Performance Optimization

Anonymous — Thu, 17 May 2018 13:21:28 GMT

Did you not get an improvement trying what I suggested?

Re: Job Performance Optimization

Anonymous — Fri, 18 May 2018 08:01:10 GMT

Hello,

Could you please upload your whole work flow screenshot on forum?

The database connection could affect the job performance. The job always runs better if the database is installed on local, if the database is on another machine, even you are on VPN, you may have the congestion and latency issues.

Best regards

Sabrina

Re: Job Performance Optimization

abhi90 — Fri, 18 May 2018 16:57:33 GMT

Hi @xdshi, @rhall,

Please find my Job Screenshot attached.I am going to attach my remaining 3 Snapshots in Next Comment.

Hi @rhall,

Yess I have tried your options. But still no Improvement Dont know How I will improve my Job performance littlebit Better.

I have tried today toracleBulkExec. Data File was generating but was getting

SQL*Loader-704: Internal error: ulconnect: OCIEnvCreate [-1] Error in toracleBulkExec component

Job_Overall.JPG
tmap.JPG
tmap_ERROR_FLAG.JPG
toracleinput_Advanced.JPG

Re: Job Performance Optimization

abhi90 — Fri, 18 May 2018 16:58:13 GMT

Remaining 3 Snapshots of Job

toracleinput_Basic.JPG
toracleoutput_Advanced.JPG
toracleoutput_Basic.JPG

Re: Job Performance Optimization

Anonymous — Fri, 18 May 2018 17:26:38 GMT

What was the performance before you made the changes I suggested, and what was it after (rows per second)?

Re: Job Performance Optimization

abhi90 — Mon, 21 May 2018 09:37:25 GMT

Hi @rhall,

with keeping Batch Size as 6000 got Throughput as 983R/Sec,with 9000 got Throughput as 993R/Sec.