topic Re: Reduce tOracleInput/Outpu's performance in Talend Studio

Reduce tOracleInput/Outpu's performance

_AnonymousUser — Sat, 16 Nov 2024 12:04:07 GMT

Hi all,
I'm working with Oracle database with the transform model like the model below and have to work with millions records or more.

CSV/XLS------------TMap--------------tOracleOutput
|
|
tOracleInput
When running this model, it's seem like that the performance of tOracleInput/Output components was not good.
Are there any similar components or any way to reduce the performance, pls help me!
Sorry for my poor english and thanks for help!

Re: Reduce tOracleInput/Outpu's performance

Anonymous — Fri, 29 Mar 2013 04:22:25 GMT

Hi,

Are there any similar components or any way to reduce the performance

Your purpose is increasing the performance or reducing?
If the former, I think these articles will be helpful for you.
Please review Storing the lookup flow of tMap on the disk, Allocating more memory to Talend Studio and outOfMemory
In addition, how many rows will be processed in your job? and what is the speed in present?
Best regards
Sabrina

Re: Reduce tOracleInput/Outpu's performance

_AnonymousUser — Fri, 29 Mar 2013 04:49:52 GMT

Sorry, I mean increasing
and the problem is not because of tMap because it can be change by another components like tFilter...
my problem's how to use tOracleIn/Output with the most effectively way

Re: Reduce tOracleInput/Outpu's performance

Anonymous — Fri, 29 Mar 2013 08:24:54 GMT

Hi,
You can set the commit size and use batch size in tOracleOutput Advanced settings to improve your job performance.
Commit every: Enter the number of rows to be completed before committing batches of rows together into the DB. This option ensures transaction quality (but not rollback) and, above all, better performance at execution.
Use Batch Size: When selected, enables you to define the number of lines in each processed batch.
Please see the component reference tOracleOutput.

Re: Reduce tOracleInput/Outpu's performance

_AnonymousUser — Mon, 01 Apr 2013 04:20:53 GMT

Hi Sabrina,
Thanks for help. I will try it.
And also, I think my problem's the same with this topic: https://community.talend.com/t5/Archive/resolved-trestclient-as-lookup-into-map/td-p/182213
I try to insert 1million records of dummy data into an oracle db and here is the result:
- with tOracleConnection:
+ Auto commit: 855.04s - 1169.54 rows/s
+ Without auto commit: 837.92s - 1193.44 rows/s
- without using tOracleConnection:
+ tOracleOutput: 16.74s - 59726.45 rows/s
The results are too different!

Re: Reduce tOracleInput/Outpu's performance

Anonymous — Mon, 01 Apr 2013 06:08:45 GMT

Hi,
If you use tOracleConnection component, there is no solution to avoid those performance falls so far. Batch load is deliberately disabled when using existing connection, because of memory problems.
In addition, the performance 16.74s - 59726.45 rows/s is not bad, I think.
Best regards
Sabrina

Re: Reduce tOracleInput/Outpu's performance

Anonymous — Mon, 01 Apr 2013 17:55:46 GMT

Actually ther is no good reason to disable the batch mode if using an external connection. All batches are stored within the statement and not in the context of the connection. This should be investigated and changed!

Re: Reduce tOracleInput/Outpu's performance

Anonymous — Tue, 02 Apr 2013 03:02:36 GMT

Hi,
When you check the "use connection option". The batch mode is no longer activated and without this option performance significantly drops. Perhaps it's an oracle constraint or only an "omission" in the component's code. We will make a search on it, thanks for the suggestion.
By the way,@hina if you don't check the "use connection option", we suggest that use a jdbc connection (based on SID) in your tOracleInput component not OCI or others.
Best regards
Sabrina