One Star

Slow performance on insert when using tOracleConnection

I've noticed a significant performance issue when I use tOracleConnection component to insert data. My environment is Windows XP, Talend IS 3.2.2, with an Oracle database. Here's the scenario:
Use tOracleConnection component to establish a connection to the database.
Create tOracleInput component using the existing tOracleConnection.
Map data into tOracleOutput component using the existing tOracleConnection.
I find that the data will flow into the tOracleOutput component extremely slowly using this technique (approximately 2-4 rows per second).
However, if I clear the "Use Existing Connection" checkbox on the tOracleOutput component and use either a repository connection or a built it connection the same program processes data much faster (several thousand rows per second).
Can someone explain to me why I experience such a performance hit when using an existing connection for the Output component? (I don't notice the same issue with the input component.
Thanks in advance.
Joe
14 REPLIES
Community Manager

Re: Slow performance on insert when using tOracleConnection

Hello
(approximately 2-4 rows per second)

It is rather abnormal, make sure you connecte to the same database with tOracleConnection and repository conection. Try to restart db, TIS and try again.
Best regards
shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Slow performance on insert when using tOracleConnection

Thanks, Shong. I have tried all of those things to no avail. Even the Talend intsructor noted this same issue when he was on site to do training.
One Star

Re: Slow performance on insert when using tOracleConnection

Hello everybody,
Sorry to re-open this post but I have the same problem than baird123.
I have a table with 1 000 000 lines to load into another table.
With the tOracleConnection : 700 lines/sec
Without the tOracleConnection : 3500 lines/sec
Is there any solution to solve this problem since baird123 wrote this post ?
I'm on TOS 3.2.1 r31371
Thanks
One Star

Re: Slow performance on insert when using tOracleConnection

Hello,
I write this post because I have the same problem too. My environnement is TOS 4.0.1 on Linux.
I have a tMap output with 840 000 lines to load into a table.
With the tOracleConnection : 1170 lines/sec
Without the tOracleConnection : 16800 lines/sec
When I see the generated code in the main section of the tOracleOutput component, I notice that :
With the tOracleConnection :
- for each row, the java.sql.PreparedStatement.executeUpdate method is called
Without the tOracleConnection :
- the java.sql.PreparedStatement.executeBatch method is called only when there is N rows (N is the 'Batch Size' parameter)
I am not a Java expert (nor jdbc) so I can't make more analyze.
Thanks in advance
Employee

Re: Slow performance on insert when using tOracleConnection

In the "advanced settings" tab of the component, did you check "Use cursor" option ? If not do it and try to play with the cursor size until you find which is best value for you...
One Star

Re: Slow performance on insert when using tOracleConnection

I can see this option in the tOracleInput component but I am not using it.
My problem is on the output of a tMap -> tOracleOutput where there is no "Use cursor" option
Regards
Employee

Re: Slow performance on insert when using tOracleConnection

sorry flemhart bad reading of your post. In the tOracleConnection can you try to check the "Autocommit" option ?
One Star

Re: Slow performance on insert when using tOracleConnection

I am just trying to select 'autocommit' on the tOracleConnection component.
There are bad performances because it makes a commit for each row and there are 840 000 row to load in table.
As I said in my previous post, there are differences in the generated code of tOracleOutput component ('main part' in the code viewer) when I use an existing connection or not.
And I would prefer to use an existing connection before loading my table and make a commit only when my subjob (tMap + tOracleOutput) is OK.
One Star

Re: Slow performance on insert when using tOracleConnection

I'm just posting to let everyone know that the "user cursor" option makes no difference. I already tested this option before the original post.
One Star

Re: Slow performance on insert when using tOracleConnection

I use the tOracleConnection for lookups with the parameter "use cursor". And for the Output (tOracleOuput), i use a direct connection (without tOracleConnection), but configured by context, and the parameter "advances settings>Commit every" configured to 10000, performances are better.
Can you try ?
One Star

Re: Slow performance on insert when using tOracleConnection

Yes shalouf, I've tried your suggestion in the past and had the same results that you are describing. The problem is that we should be able to use a tOracleConnection on the tOracleOutput. In your situation, you're requiring the Talend job to create a second connection to the database, which should not be required. The tOracleConnection component is intended to prevent the need for creating a new connection with each component. We should be able to use this component without experiencing performance degradation.
One Star

Re: Slow performance on insert when using tOracleConnection

One Star

Re: Slow performance on insert when using tOracleConnection

I did also notice slow performance and activated "parallel execution". This seriously increases performance! However, I'm having two problems, please see my post here: http://www.talendforge.org/forum/viewtopic.php?id=27678
One Star

Re: Slow performance on insert when using tOracleConnection

Yes shalouf, I've tried your suggestion in the past and had the same results that you are describing. The problem is that we should be able to use a tOracleConnection on the tOracleOutput. In your situation, you're requiring the Talend job to create a second connection to the database, which should not be required. The tOracleConnection component is intended to prevent the need for creating a new connection with each component. We should be able to use this component without experiencing performance degradation.

Absolutely, whenever you check Use Existing Connection , immediatly Commit Every and Batch Size options will be vanished. this should not be the case.