topic Re: Very less throughput for tOracleInput component in Talend Studio

Very less throughput for tOracleInput component

Anonymous — Wed, 22 May 2019 08:19:05 GMT

Hi,

I have a very simple query selecting a set of columns from a particular table using tOracleInput component, followed by a tmap and then tOracleOutput. There is a small lookup table with the input component. But the throughput of tOracleInput is very very low (around 3 rows/sec) which is not acceptable for loading 650-700k records.

I am not using any tOracleConnection component. I also saw that the job firsts reads all the rows from the lookup table and then starts reading from the main Input component.

Can anybody help me out with the issue. It needs urgent resolution from our side so an early response would be highly appreciated!

Re: Very less throughput for tOracleInput component

Anonymous — Wed, 22 May 2019 11:01:46 GMT

can you please send screenshots?

how are you handling the commits?

Re: Very less throughput for tOracleInput component

Anonymous — Wed, 22 May 2019 11:14:40 GMT

Attached is an image of the job structure.

I had tried enabling cursor for 20000 rows but that did not help.

Lookup is following 'Load once' method.

I have not explicitly handled commit in the job.

Talend.png

Re: Very less throughput for tOracleInput component

Anonymous — Wed, 22 May 2019 11:48:12 GMT

Are you using tSCD or tDBOutput?

The picture you send does not show the connection with the last component

Do you have any key constrain in the table

we can't help if you don't provide enough infos

Re: Very less throughput for tOracleInput component

Anonymous — Wed, 22 May 2019 12:05:20 GMT

I am using a tOracleSCD component. The last component is a tOracleRow and I did not include it in the picture because I am certain that it is not contributing to the issue. The delay is in the fetch/ read of the data from source into tmap, and subsequently into the SCD component. When i am using a filter like rownum<=100, the job is completing with a minor time taken to commit the operation in the last tOracleRow.
The table has one unique index on the surrogate key. Other than that, it has few not null constraints.
I am using Talend Data Integration 6.2.1. The settings of all the components mentioned are set as in the default mode, I have not explicitly mentioned any additional criteria.
Also, please let me know what other information you need.

Re: Very less throughput for tOracleInput component

Anonymous — Wed, 22 May 2019 14:02:02 GMT

Thanks for clarification

I think the problem might be the sql request you are using to fetch the data.
I suggest you test the performances of the same sql request with another tool (toad, ..)

Re: Very less throughput for tOracleInput component

Anonymous — Wed, 22 May 2019 14:08:02 GMT

Hi,

The SQL code when tested in DBeaver takes only around 5-7 secs. Hope this clarifies.

Re: Very less throughput for tOracleInput component

Anonymous — Wed, 22 May 2019 14:20:42 GMT

Sorry, I don't see why it should be slow, but for years we have used it to from our oracle database from a table of 150.000.000 records per day.

Re: Very less throughput for tOracleInput component

Anonymous — Wed, 22 May 2019 14:37:04 GMT

Hi @NinaD ,

Did you get a chance to look at the explain plan of the input query you are using? Are the costs looking normal? Also the machine where you used DBeaver and the machine where Talend Studio is running are same?

Please also check whether there is any throughput constraints from network side too.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: Very less throughput for tOracleInput component

Anonymous — Thu, 23 May 2019 07:42:27 GMT

Hi,

Yes the two are running in the same machine. The query goes like :

SELECT
a1,a2,a3.... aN from source where source.last_update_dtm>(select max(dtm) from target);

The explain plan results are attached.

Hope this helps.

Talend2.png

Re: Very less throughput for tOracleInput component

Anonymous — Thu, 23 May 2019 14:45:24 GMT

Hi,

The cost looks decent although it is full table scan (since its taking maximum value, we will have to live with that).

Are you facing issue when running the same query from Talend? Could you please increase the memory parameters from current value to double in advanced area of run tab and see the results?

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂