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