Fast copy of records between 2 Oracle tables

One Star

Fast copy of records between 2 Oracle tables

Hi everyone,
I have created a small job that is supposed to copy data records from one Oracle-table to another.
The job works as follows:
1. Create DB connection
2. Truncate target table
3. Use tOracleInput to implement a "select * from source_table"
4. Use tOracleOutput to "insert or update" on the target table
5. Close connection
As you can see in the attached screenshot, it works - but it's extremely slow.
Any ideas on how to improve the performance by at least 10x ? (1000x faster would be great)
Thanks
Matt
Four Stars

Re: Fast copy of records between 2 Oracle tables

Hi kleinmat - you can check your jvm settings either for your studio of your job itself to give it more umpf... See http://kindleconsulting.com/blog/entry/managing-jvm-heap-size-in-talend-open-studio.html
OR
Redesign your job to use Oracle ELT components... With ELT, Talend generates a SQL statement that is executed server-side. Your current job pulls all the records to your Talend execution server in batches, which is slower based on how much memory you have...
One Star

Re: Fast copy of records between 2 Oracle tables

Wow, great! Thank you so very much (again) for your help.
I have one issue, though: I created a "generic schema" representing the source table. The table name has a $ in its name. My Studio keeps changing that $ to _
So while the Oracle table is actually called "THO$TA_STUFF", Studio changes it to "THO_TA_STUFF", which naturally results in a "table or view does not exist"-exception.
Any idea how to circumvent this?
Thanks!
Four Stars

Re: Fast copy of records between 2 Oracle tables

You're welcome, Matt...!
I believe you're getting that issue because the metadata is being stored in the Talend repository and it doesn't allow the $ in schema object names... When you actually use the schema in your job, you'll be able to set the real table name, and Talend will not change it (or should not change it).
Let us know if that works...
Four Stars

Re: Fast copy of records between 2 Oracle tables

Hi kleinmat,
Reading and writing data to and from the database also depends upon the commit cycles... Earlier i remember a scenario for similar optimization is load data from database to local flat file and use bulk load component for respective database to load into the database.
This also gives better improvement in data loading...
Check if you want to add this improvement to your existing job in addition to what willm suggested in his post.
Thanks
Vaibhav
One Star

Re: Fast copy of records between 2 Oracle tables

Thanks guys for your great and super fast help.
@willm, thanks for clarification. I did as you said but the code generator of Talend still changes the name. I have created a bug report and attached screenshots here: https://jira.talendforge.org/browse/TDI-29162
Looks more like a design flaw than a bug to be, but I hope they will fix it :-)
@sancaibhav, thanks for your suggestion. I think I will try to stick with the ELT solution for now as it enables the data transfer entirely within the database (by means of a merge-statement). But if it doesn't work e.g. due to that $ replacement issue I mentioned, I will certainly follow your advice.
Thanks again!