Reduce tOracleInput/Outpu's performance

[_AnonymousUser]

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!

[Anonymous]

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

[_AnonymousUser]

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

[Anonymous]

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.

[_AnonymousUser]

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!

[Anonymous]

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

[Anonymous]

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!

[Anonymous]

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