Question from 'Full load but not delete record, just insert and update'

[Former Member]

Hi experts,

I'm working on a full load ( full load required based on our data) to extract and load material master data from ECC to data mart. We would like to keep the current record, and then insert new record and update the current record based on the change in ECC. I unchecked on the option 'delete record from table before loading' which is able to keep the old record. However, I found that the existing record is not able to do  update if I changed some on this record in ECC. In order to make sure the target table could keep old record, update existing record and insert new record, I have to use 'Table_comparsion' . After this, it did work. But the performance is so bad. It took almost two hours to finish a full load which includes 124700 records. This is painful. 

Does anyone has some suggestions on how to do this? Appreciated for any input.

Thanks!

Cindy

[Former Member · Accepted solution]

In TC which option are you trying - Row by row or Sorted? Try sorted and check how much time it takes.

[Former Member · Accepted solution]

Hi Arun,

Thanks for your suggestion. I implemented the sorted one. For the first 4900 rows was pretty quick, took only 1min. And after that, it seems like stuck. The time is running, but the row count did not change. Now, it is still 4900 after 1561 secs.

[Former Member · Accepted solution]

Did you order by before the TC? Add a query transform before TC and insert Orderby columns in the order of keys in TC.

[Former Member · Accepted solution]

I'm trying the last one, "Cashed compare table' hope it helps!

[Former Member · Accepted solution]

Sorted comparison is faster than the other two. Only thing you have to order the key columns prior to TC. Refer my previous post.

[Former Member · Accepted solution]

Really appreciate! I'm working on it and will update here! Thanks:)

[Former Member · Accepted solution]

Hi Arun,

I found that with Sorted option, I will get warning if there is an update on current record which is saying that 'the primary key' could not duplicate things. Apparently, it intended to create a new insert. How to fix that? I just would like to update if this primary key value is already in the TC.

[former_member187605 · Accepted solution]

Follow the links to the wiki pages from my document () for a detailed description of the Table_Comp&rison transform.

[Former Member · Accepted solution]

Ok. You have duplicate keys in the target. One way to fix is add a key generation transform before the target and use this key as the unique identifier or primary key.

So your flow should look like this

Source > Query (ordering)>Table comparison > Key generation > Target

[Former Member · Accepted solution]

Hi Arun,

I really appreciate your help! It did much quicker !Thanks!

[former_member187605]

Check for performance implications of settings in Table_Comparison transform.