Hi,
I have a requirement to compare a table before update and after update.
I have 10 columns in each record and table is having around 20 millions of records.
I want to run a comparison over 20 millions of records.
In this case, is it feasible to use CHANGE CAPTURE stage to get the differences (insert/update/delete) records?
If I use CPC stage, how about the performance on 4 node config file?
Any rough idea how long it may take for comparisons over 20million records?
Performance with Change Capture Stage
Moderators: chulett, rschirm, roy
Performance with Change Capture Stage
Thanks,
Raamc
Raamc
If you are doing the updates then you already have that information without having to go to the actual table, right?
Performance guesstimates are impossible at this time - are you running on a single-CPU 486 with 100 other users or do you have a 32 Processor SMP system all to yourself? Is the database local or remote and how tuned is it? Even with those answers it is impossible to guess (are your records 10 bytes or 10000 bytes wide?)...
Performance guesstimates are impossible at this time - are you running on a single-CPU 486 with 100 other users or do you have a 32 Processor SMP system all to yourself? Is the database local or remote and how tuned is it? Even with those answers it is impossible to guess (are your records 10 bytes or 10000 bytes wide?)...
Thanks Arndw,
I am not doing the updates. Some other system will do the updates and that updates will flow into my system. I can find changes only by comparing before and after images.
Any performace tunning methods before using CHANGE CAPTURE stage. I am sorting the data other than this any other thing i need to do?
Witgout using the CPC stage, is there any other way to find update/delete/insert records?
I am not doing the updates. Some other system will do the updates and that updates will flow into my system. I can find changes only by comparing before and after images.
Any performace tunning methods before using CHANGE CAPTURE stage. I am sorting the data other than this any other thing i need to do?
Witgout using the CPC stage, is there any other way to find update/delete/insert records?
Thanks,
Raamc
Raamc
Thanks Arndw,
I am not doing the updates. Some other system will do the updates and that updates will flow into my system. I can find changes only by comparing before and after images.
Any performace tunning methods before using CHANGE CAPTURE stage. I am sorting the data other than this any other thing i need to do?
Witgout using the CPC stage, is there any other way to find update/delete/insert records?
I am not doing the updates. Some other system will do the updates and that updates will flow into my system. I can find changes only by comparing before and after images.
Any performace tunning methods before using CHANGE CAPTURE stage. I am sorting the data other than this any other thing i need to do?
Witgout using the CPC stage, is there any other way to find update/delete/insert records?
Thanks,
Raamc
Raamc
HI RAAMC,
There is an alternative for change capture stage.use a join stage with join type as 'full outer join'.Hash partition both the input links(sort,null first and ascending).
You can easily figure out the deletes,inserts and updates in the new data.use a tranformer stage following the join stage.In the constraint give the following constraints
Inserts-->when the old_link.Key=''
Deletes-->when the new_link.key=''
Updates-->when old_link.key<>'' and new_link.key<>''.
This would help you.Actually this would help you .I have tried this logic for two tables each containing more than 20 million records.
Regards,
Raja R P[/code][/quote]
There is an alternative for change capture stage.use a join stage with join type as 'full outer join'.Hash partition both the input links(sort,null first and ascending).
You can easily figure out the deletes,inserts and updates in the new data.use a tranformer stage following the join stage.In the constraint give the following constraints
Inserts-->when the old_link.Key=''
Deletes-->when the new_link.key=''
Updates-->when old_link.key<>'' and new_link.key<>''.
This would help you.Actually this would help you .I have tried this logic for two tables each containing more than 20 million records.
Regards,
Raja R P[/code][/quote]
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: