Talend community forum / Performance decrease using JDBC componant

EDICdiscount · 2012-04-17 12:07:25

Hello,

i'm facing some very bad performance using tJDBC componants in order to select and load from/into MSSQLserver database.
i don't want to use tMSSQL componants in my case!
For you information i have a job which select top 1000 from table1 and update each line in table1 on one field.
With tJDBCInput only i have 5952 rows/s
with tJDBCInput and tJDBCOutput i have 250 rows/s
with tMSSQLInput and tMSSQLOutput i have 18181 rows/s

JDBC driver is sqljdbc4.jar
classe=com.microsoft.sqlserver.jdbc.SQLServerDriver

Do you have any requirement to improve performance ? Maybe using an another driver ?

I have tested with following drivers also but got same bad performance:
talend_DB_mssqlUtil.jar and jtds-1.2.5.jar

Thanks for your response

pedro · 2012-04-18 08:50:26

Hi

In fact, from Java code aspect tJDBCInput and tMSSQLInput are the same.
I have tested them with jtds driver and get silimar performance.

So it may be due to job design.
Or i miss some detail?

Regards,
Pedro

Uploaded Images

EDICdiscount · 2012-04-18 10:47:58

Thanks for your response Pedro.

But i have tested with jtds driver with following values and get same bad performance:

context.urlJDBCexp=jdbc:sqlserver://serverName\instanceName;databaseName=CDISCOUNT_EXPEDITION
classeNameMSSQL=com.microsoft.sqlserver.jdbc.SQLServerDriver

Please find attached screen capture

Thanks for your support !

Uploaded Images

pedro · 2012-04-18 10:55:15

Hi

The class name should be 'net.sourceforge.jtds.jdbc.Driver' if you want to use jtds driver.

Regards,
Pedro

EDICdiscount · 2012-04-18 11:16:36

Same thing Pedro with 'net.sourceforge.jtds.jdbc.Driver' :
148 rows/s with tJDBC and 6666 rows/s with tMSSql

Any other idea ?

Thanks

pedro · 2012-04-18 11:23:36

Hi

I think the bad performance is not caused by JDBC components or MSSQL components.
Because if running them separately, you would get similar performance.

This may be due to your job logic or something else.

Regards,
Pedro

EDICdiscount · 2012-04-20 14:05:47

Pedro,

I have tested on another job using jtds and simplify it only with Input->tMAP->Output (see JDBCvsMSSQL_1.png)
I still get bad perfomances with JDBC.

I have attached Component property screen shot with URL JDBC, Driver and classeName used.

Do you have any advice for my problem ?

For your information, i'm using TIS 4.2.4. java

Thanks for your support !

Uploaded Images

pedro · 2012-04-23 06:35:52

Hi

This is the job I create with instance name and nolock in query.

Is there any special settings in tMap which may have an effect on performance?

Regards,
Pedro

Uploaded Images

EDICdiscount · 2012-04-23 18:23:54

Nothing special in tMAP, just pass 2 fields.
I have tested without tMAP and tJDBCOutput as you can found in my screen shot.
Still get bad performances :-(

Uploaded Images

elvis · 2012-04-23 19:08:37

Is performance hit of the same order of magnitude over more rows (i.e. is the problem just that the JDBC connector takes a while to get going?)

pedro · 2012-04-24 04:35:59

Hi

Which version of TOS do you use? Which OS, 32bit or 64bit?
I think this is due to your current system environment.
I tested this issue under TOS 5.0.2.

Regards,
Pedro

EDICdiscount · 2012-04-25 18:11:56

Hi,

I'm using TIS 4.2.4 under Seven 64bit

Regards

pedro · 2012-04-26 05:08:31

Hi

Since you have reported a bug on JIRA, I think you'd better follow Michaël Hirt's suggestion.

Code:

MS SQL components are optimized for MS SQL
JDBC components must remains generic and therefore it might not be optimized for all databases.
Any change that we could do here could decrease performance of any other database.

http://jira.talendforge.org/browse/TDI-20778

Regards,
Pedro

Announcement

Unanswered posts

#1 2012-04-17 12:07:25

Performance decrease using JDBC componant

#2 2012-04-18 08:50:26

Re: Performance decrease using JDBC componant

#3 2012-04-18 10:47:58

Re: Performance decrease using JDBC componant

#4 2012-04-18 10:55:15

Re: Performance decrease using JDBC componant

#5 2012-04-18 11:16:36

Re: Performance decrease using JDBC componant

#6 2012-04-18 11:23:36

Re: Performance decrease using JDBC componant

#7 2012-04-20 14:05:47

Re: Performance decrease using JDBC componant

#8 2012-04-23 06:35:52

Re: Performance decrease using JDBC componant

#9 2012-04-23 18:23:54

Re: Performance decrease using JDBC componant

#10 2012-04-23 19:08:37

Re: Performance decrease using JDBC componant

#11 2012-04-24 04:35:59

Re: Performance decrease using JDBC componant

#12 2012-04-25 18:11:56

Re: Performance decrease using JDBC componant

#13 2012-04-26 05:08:31

Re: Performance decrease using JDBC componant

Code:

Board footer