topic Re: Really Slow load from Informix Database (version 7.3) in Talend Studio

Really Slow load from Informix Database (version 7.3)

Anonymous — Thu, 18 Apr 2019 22:14:52 GMT

Hello!

I would like help with a really big problem that I am facing right now, I have load data from an Informix database but Talend can't load more than 70 rows per second, if I try to load in this same database from Excel or Qlik I can read it at least 8 x faster.

Could someone help me with that?

Re: Really Slow load from Informix Database (version 7.3)

Anonymous — Fri, 19 Apr 2019 00:03:09 GMT

Hello,

I figured the problem could be the time to convert the local database format to Talend format if I take off varchar/string field the load speed increase more than 3 times.

but I can't find a solution to improve that, because of the varchar field is a key in this table.

Someone, could me provide some help, please?

tks!

Best Regards!

Re: Really Slow load from Informix Database (version 7.3)

vapukov — Fri, 19 Apr 2019 07:43:01 GMT

Hi,

there is must be no problem with the conversion but easily could be a problem with data size

reading speed depends from:

query - how it complicated and how fast DB start returning data
data size
network latency
JDBC driver performance

Re: Really Slow load from Informix Database (version 7.3)

Anonymous — Fri, 19 Apr 2019 13:00:38 GMT

Thank you @vapukov,
But the query is a simple select from, if I use the same query in a odbc connection in Execel or Qlik for example I get a considered better reply.
I have made some test in talend, if I take off the varchar field the velocity increases to 140 rows/sec if I get just 3 fields (integer) I can load 3000 row/sec.
But I need all of the fields, if I consider all I can't pass than 70 rows/sec.

All the test have been made un the same server with the same environment.

Re: Really Slow load from Informix Database (version 7.3)

vapukov — Fri, 19 Apr 2019 13:07:45 GMT

ok, do you really output all 50k+ rows to tlogrow?

is the speed same if output is CSV file?

Re: Really Slow load from Informix Database (version 7.3)

Anonymous — Fri, 19 Apr 2019 13:15:12 GMT

I landing more than 200 k but I just output the last 100 rows.
I didn't test with csv, I am going to test it and I inform you.

Re: Really Slow load from Informix Database (version 7.3)

vapukov — Fri, 19 Apr 2019 13:16:19 GMT

if you print just 100 rows, it is not important

Re: Really Slow load from Informix Database (version 7.3)

vapukov — Fri, 19 Apr 2019 13:37:59 GMT

I afraid there no simple answer, need to check all what I mention above, because simple test with 3 int and 1 string (255 characters):

so, again:

- size (maybe your "string" is 32000 char 🙂 )

- network

- etc

add:

if you are IBM customer, try to download fresh JDBC drivers and test with tJDBCConnection / tJDBCInput

Re: Really Slow load from Informix Database (version 7.3)

Anonymous — Fri, 19 Apr 2019 13:59:16 GMT

Actually this string there is 22 characters, it is a fixed alphanumeric field.
I have to check my jbdc driver, but the wired thing about it is if I retrieve different fields in the same table I have different performances.
I believe in the time when I load the data source formats Talend spend a large time to transform for a Telend data type, I don't know if has a better way to do that.

Re: Really Slow load from Informix Database (version 7.3)

vapukov — Fri, 19 Apr 2019 14:01:03 GMT

my picture example from Informix, with 255 mixed string, so - Talend don't spend time for this

Re: Really Slow load from Informix Database (version 7.3)

Anonymous — Fri, 19 Apr 2019 15:11:19 GMT

Could you share your schema? Maybe I am get a wrong data type. What's is your jbdc driver version ?
I

Re: Really Slow load from Informix Database (version 7.3)

vapukov — Fri, 19 Apr 2019 15:14:31 GMT

I just create and populate table with 1M rows, as you describe:

- serial - primary key

- integer - random

- integer random

- varchar - 255 char random

and use standard talend components - int, int, int, string

Re: Really Slow load from Informix Database (version 7.3)

Anonymous — Fri, 19 Apr 2019 15:18:32 GMT

It is wired I have used the same data types as you.....
In the same server I have different performance to load the data depends on the tool.
I really don't know how fix it...
Maybe the version of the informix 7.3

Re: Really Slow load from Informix Database (version 7.3)

vapukov — Fri, 19 Apr 2019 15:20:18 GMT

yes, it could be and settings and version (I have the latest) and network - my is local 1Gb network

Re: Really Slow load from Informix Database (version 7.3)

Anonymous — Fri, 19 Apr 2019 15:22:47 GMT

I have the database In the same network, i don't believe this is the reason...
As I said before, the wired thing is I have a good performance In the same table delwnd the field I retrieve

Re: Really Slow load from Informix Database (version 7.3)

Anonymous — Tue, 23 Apr 2019 21:37:12 GMT

I have tested with Pentaho and get better performance, but I would like to do it in Talend, someone I am convinced that the problems are about the Data formating between Talend and Informix

Re: Really Slow load from Informix Database (version 7.3)

Anonymous — Thu, 25 Apr 2019 14:35:36 GMT

The problem was the limitation from Informix to retrieve the data, you have to set the variable FET_BUF_SIZE to improve the performance. more details here
https://community.talend.com/t5/Design-and-Development/Problems-to-connect-Informix/m-p/159228#M97186

I really thanks to @vapukov