topic Re: Perfomance Issue for Large Dataset using Postgres SCD component in Talend Studio

Perfomance Issue for Large Dataset using Postgres SCD component

Anonymous — Wed, 03 Oct 2018 09:12:00 GMT

Hi,

I am using Postgres SCD component to load data into my target dimension table using SCD2, but it is taking much time to load for larger data set. It took average 2 records per second for 1.5 million records, even target table had 0 records when process start.

Could you please help to explain what could be the reason?.

Re: Perfomance Issue for Large Dataset using Postgres SCD component

vapukov — Fri, 05 Oct 2018 07:33:51 GMT

some percents of performance you can tune on database size - memory, indexes

but generally it always slow not depending is it PostgreSQL or MS SQL, same issue for insert/update - insert very fast, insert/update max 5 rows per second

try to look for process and do the same without using SCD components:

select all changed from last date (by any way - trigger, modifiedDT etc)
transfer all to target database into stage table
run 2 query - UPDATE with JOIN all old records (set isActive to False), INSERT all new records with isActive True

it would be faster overall

Re: Perfomance Issue for Large Dataset using Postgres SCD component

Rajesh_Reddy1 — Fri, 05 Oct 2018 12:30:36 GMT

Hi,

Its better if you create SCD work flow job, instead of SCD components.

Re: Perfomance Issue for Large Dataset using Postgres SCD component

Anonymous — Fri, 05 Oct 2018 13:40:58 GMT

Thank you so much

Re: Perfomance Issue for Large Dataset using Postgres SCD component

Anonymous — Fri, 05 Oct 2018 13:43:12 GMT

okk..
Thanks Rajesh

Re: Perfomance Issue for Large Dataset using Postgres SCD component

Anonymous — Fri, 05 Oct 2018 13:43:43 GMT

Thank you so much