PostgreSQL instant query gets slow in Talend

[Anonymous]

Hello,

I am working with a PostgreSQL database, TOS 6.5.1 and struggling with a minor error.

I have a fairly simple query that deletes some rows (usually 0) in case of date conflict.

I noticed the query was taking way too much time and realized that the query is pretty much instantaneous (<1s) in psql or pgAdmin but will take somewhere between 20 minutes and 2 hours to complete in Talend.

It was first used as a function in a tPostgreSQLrow (select * from schema.function) then in a second time using only the query as-is.

I tested a lot using the exact same query, both in Talend and pgAdmin and the time difference remained.

I tried to use a tPostgresSQLconnection instead of built-in with the same result and I'm running out of ideas.

Is there something wrong with the jdbc connection or am I doing something wrong ?

Best regards,

Damien

[Anonymous]

Hi Damien,

     Could you please try the same query in 7.1 version of Studio? There are upgrades in version 7 of Talend and it may give better performance. You can check the same with Open source version of Talend.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

[vapukov]

we have many jobs done on 6.4/6.5 with PostgreSQL, not similar issues was detected

so, need more information about architecture and job design

[Anonymous]

Hi Nikhil,

Thank your for your answer, since we recently upgraded to Talend 6.5, it might be difficult to justify another upgrade so soon.

Nonetheless, I'll try to install the latest version to test if I manage to save some time.

Best regards,

Damien

[Anonymous]

Hi Vapukov,

It surprises me too since we have a lot of jobs on PostgreSQL and none display this behavior.

To give you more information, I attached a picture of the job to this message, circled the one with the issue.

I have some homemade logs used with tRunJob that allows me to know the duration of each step.

The component in question is a tPostgresqlRow with the following query :

"DELETE FROM " + context.EPE_CNT_SCHEMA + ".qc1_epe_wtb_hist_3
WHERE ctid IN (SELECT DISTINCT (CASE WHEN t1.dat_start = t1.dat_end THEN t1.ctid
WHEN t2.dat_start = t2.dat_end THEN t2.ctid END) AS ctid
FROM " + context.EPE_CNT_SCHEMA + ".qc1_epe_wtb_hist_3 t1
, " + context.EPE_CNT_SCHEMA + ".qc1_epe_wtb_hist_3 t2
WHERE t1.key_item = t2.key_item
AND t1.dat_start = t2.dat_end
AND ( t1.dat_start = t1.dat_end OR t2.dat_start = t2.dat_end )
AND t1.ctid <> t2.ctid);"

With "context.EPE_CNT_SCHEMA" allowing me to change the schema depending on my configuration.

The query runs under a second in pgAdmin or psql but goes on and on if I call it with this component.

Do you need me to provide more details ?

Best regards,
Damien

job.JPG

[Rahul_Lohar]

any update on this