[resolved] tOracleInput to tOracleOutput very slow

Highlighted
One Star

[resolved] tOracleInput to tOracleOutput very slow

Hi folks,
I'm suffering from the problem that a simple copy from one table to another is very slow. The tables are on different schemas but on the same database. The Oracle Input is a select-query with a simple WHERE clause.
I'm connected to the database server via VPN. Maybe this is the limiting factor? But still the operation should be faster than ~5 rows per second....
See attached Screenshot.

Accepted Solutions
One Star

Re: [resolved] tOracleInput to tOracleOutput very slow

assuming your database is healthy this is probably a limitation of the network throughput between the database and where talend is running. Some options are to avoid transferring data across the network are:
* tOracleRow - if you can write your process as a single sql statement ( insert into ... select from )
* tOracleSP - put your process in a stored procedure and call it
* use ELT components - tELTOracleInput, tELTOracleMap, tELTOracleMap
there is a bit of a learning curve with the ELT components as they work quite differently than other components. This is because they primarily for generating SQL.

View solution in original post


All Replies
One Star

Re: [resolved] tOracleInput to tOracleOutput very slow

assuming your database is healthy this is probably a limitation of the network throughput between the database and where talend is running. Some options are to avoid transferring data across the network are:
* tOracleRow - if you can write your process as a single sql statement ( insert into ... select from )
* tOracleSP - put your process in a stored procedure and call it
* use ELT components - tELTOracleInput, tELTOracleMap, tELTOracleMap
there is a bit of a learning curve with the ELT components as they work quite differently than other components. This is because they primarily for generating SQL.

View solution in original post

One Star

Re: [resolved] tOracleInput to tOracleOutput very slow

assuming your database is healthy this is probably a limitation of the network throughput between the database and where talend is running. Some options are to avoid transferring data across the network are:
* tOracleRow - if you can write your process as a single sql statement ( insert into ... select from )
* tOracleSP - put your process in a stored procedure and call it
* use ELT components - tELTOracleInput, tELTOracleMap, tELTOracleMap
there is a bit of a learning curve with the ELT components as they work quite differently than other components. This is because they primarily for generating SQL.

Mike, you are right, I run the job at a machine which is on the same subnet as the database and the throughput was much better.
I already tried ELT but gave up to quick maybe ^^ Will take a look at it again, thanks anyway!
Four Stars

Re: [resolved] tOracleInput to tOracleOutput very slow

In addition to that, check your jvm settings for your Studio - they may be low.... https://help.talend.com/search/all?query=Allocating+more+memory+to+the+Talend+Studio&content-lang=en

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog