DSXchange

Currently I am facing the performance issue in one of the table load. This job has the CDC stage to compare source and target records based on Key fields (2 fields).
Initially job worked fine and was completing in few minutes. Now data has been increased (5M) and it is taking very long time (7Hrs) to complete. It has been observed that job takes long time to finish even there are no source records are available. All its time consuming in CDC. This job is loading the data in Oracle database.

Let's get the nomenclature right for starters. Is this really a CDC stage, or is it one of Change Capture, Compare or Difference?

Can you replicate the issue in non-prod?

If you can, eliminate one aspect at a time.

A) cut out Oracle and write to a file.


Understand your query and CDC process to see if you are doing a full table scan for each row you wish to test for changes. Are both tables properly partitioned, and "keyed" approprietly?

What are your two sources? Files or database tables?

Try to test your process with Volume of data "A" then up the data volume to "B" and test again.