topic Re: Slow performance on insert when using tOracleConnection in Talend Studio

Slow performance on insert when using tOracleConnection

Anonymous — Sat, 16 Nov 2024 13:35:25 GMT

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

Re: Slow performance on insert when using tOracleConnection

Anonymous — Wed, 27 Jan 2010 02:54:13 GMT

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

Re: Slow performance on insert when using tOracleConnection

Anonymous — Wed, 27 Jan 2010 21:42:06 GMT

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.

Re: Slow performance on insert when using tOracleConnection

Anonymous — Mon, 26 Apr 2010 10:05:27 GMT

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

Re: Slow performance on insert when using tOracleConnection

flemhart — Wed, 30 Jun 2010 14:14:39 GMT

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

Re: Slow performance on insert when using tOracleConnection

Anonymous — Wed, 30 Jun 2010 15:06:19 GMT

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...

Re: Slow performance on insert when using tOracleConnection

flemhart — Wed, 30 Jun 2010 15:18:21 GMT

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

Re: Slow performance on insert when using tOracleConnection

Anonymous — Thu, 01 Jul 2010 09:27:02 GMT

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

Re: Slow performance on insert when using tOracleConnection

flemhart — Thu, 01 Jul 2010 10:38:28 GMT

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.

Re: Slow performance on insert when using tOracleConnection

Anonymous — Thu, 12 Aug 2010 16:18:06 GMT

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.

Re: Slow performance on insert when using tOracleConnection

Anonymous — Fri, 13 Aug 2010 04:10:12 GMT

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 ?

Re: Slow performance on insert when using tOracleConnection

Anonymous — Fri, 13 Aug 2010 18:57:23 GMT

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.

Re: Slow performance on insert when using tOracleConnection

Anonymous — Fri, 20 Jan 2012 08:14:17 GMT

http://talend-training.blogspot.com/

Re: Slow performance on insert when using tOracleConnection

Anonymous — Tue, 08 Jan 2013 06:28:09 GMT

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: https://community.talend.com/t5/Design-and-Development/Parallel-execution-tOracleOutput/td-p/91778

Re: Slow performance on insert when using tOracleConnection

_AnonymousUser — Fri, 19 Apr 2013 15:20:54 GMT

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.