tOracleConnection --> performances

[Anonymous]

Hi,
I have a job in which I just load a source table into a target one by making some transformations in a tMap.
If I just have : tOracleInput --> tMap --> tOracleOutput
--> the speed is about 20000 rows/second
But here, the inconvenient is when an error occurs for the 1000th row : the first 999 rows have already been commited in the target table... And I don't want that. I would that all rows be inserted in target table if and only if the job is successful for each row.
So, I have added a tOracleConnection in pre-job and tOracleRollback and tOracleCommit at the end of my job and I don't have this last problem anymore. All rows are commited at the end of job if the job works for each row.
Another problem concerns here performances : the speed falls to 3000 rows/second...
So, I wonder if there is a solution which allows to avoid the auto-commit after each row and in the same time, performance falls I have with a tOracleConnection ?
Thanks a lot in advance

[Anonymous]

Hi,
I have seen your work flow, and there is a tMap component. Because Talend cache is stored in memory by default, you can choose the option "Store on disk".
See my screenshot for details
Best regards
Sabrina

[Anonymous]

Hi Sabrina,
Thanks a lot for your answer.
I've tested what you propose to me in the tMap.
With the tOracleConnection, I feel that this change in tMap doesn't impact speed...
Speed when I there was no path for option "Store on disk" was about 3000 rows/second and now with a path it's about 3500 rows/second
Without tOracleConnection, speed is now about 35000 rows/second
Is there a solution to avoid those performance falls while commiting all rows only if the job is entirely successful ?
Thanks in advance

[Anonymous]

Hi,

With the tOracleConnection, I feel that this change in tMap doesn't impact speed...
Speed when I there was no path for option "Store on disk" was about 3000 rows/second and now with a path it's about 3500 rows/second
Without tOracleConnection, speed is now about 35000 rows/second

If you use tOracleConnection component, there is no resolution to avoid those performance falls so far. Batch load is deliberately disabled when using existing connection, because of memory problems.
Best regards
Sabrina