Perfomance Issue for Large Dataset using Postgres SCD component

[Anonymous]

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

[vapukov · Accepted solution]

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 

[Rajesh_Reddy1]

Hi,

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

[Anonymous]

Thank you so much

[Anonymous]

okk..
Thanks Rajesh

[Anonymous]

Thank you so much