BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Follow BOB on Twitter! 
Follow BOB on Twitter! (Opens a new window)  

General Notice: BOB is going to retire...please see details here.
General Notice: No events within the next 45 days.

Autocorrect load or Table comparisons


 
Search this topic... | Search DI: Performance and Tuning... | Search Box
Register or Login to Post    Forum Index -> Data Integrator -> DI: Performance and Tuning  Previous TopicPrint TopicNext Topic
Author Message
RohitV
Forum Member
Forum Member



Joined: 21 Jun 2005

Posts: 10
Location: India


flag
PostPosted: Wed Feb 01, 2006 2:43 am 
Post subject: Autocorrect load or Table comparisons

Hello Gurus,

We got a situation where we need to take a decision what to choose Auto correct load or table comparison..

We have source tables having tons of records.. And there are some duplicate records in the source so we need to transfer all the records to target table. We have tried using autocorrect load. The ETL is working but it is taking lot of time.
Quote:

In auto correct load : if the Use input keys check box is selected, Data Integrator uses the primary key of the source table. Otherwise, Data Integrator uses the primary key of the target table; if the target table has no primary key, Data Integrator considers the primary key to be all the columns in the target.

This is how autocorrect load operates.I would like to know how Table Comparison operates?
What is the exact difference between Table Comparison and Auto correct Load?If the updates are high in the source and target table is also huge then what is the best solution ?
Back to top
Werner Daehn
Forum Devotee
Forum Devotee



Joined: 17 Dec 2004

speaker.gif*6
Posts: 10593
Location: Germany


flag
PostPosted: Wed Feb 01, 2006 4:31 am 
Post subject: Re: Autocorrect load or Table comparisons

Autocorrect load can sometimes be pushed down, TC is always an engine operation.

If not pushed down, autocorrect load always updates the row even if nothing changed whereas TC only updates changed rows. And most databases have no optimization of an update with no change.

TC has more options like what to compare, dealing with surrogate keys etc.


http://diamond.businessobjects.com/node/4685

_________________
Werner Daehn
ex Product Manager Data Services at SAP SE

www.rtdi.io


Last edited by Werner Daehn on Sat Dec 22, 2007 4:09 am, edited 1 time in total
Back to top
RohitV
Forum Member
Forum Member



Joined: 21 Jun 2005

Posts: 10
Location: India


flag
PostPosted: Mon Feb 06, 2006 7:26 am 
Post subject: Re: Autocorrect load or Table comparisons

Werner first of all thanks a lot for the helpful information.
But I'll need more: icon_biggrin.gif
Can you please tell me operation level difference between Table Comparison and Auto Correct Load. How it works?
I got few queres because I dont understand a clear difference between them. icon_lol.gif
1.Are these two substitute for each other?
2.Whether in Table Comparison(row by row method) it checks each source row with entire target table rows or only 1000 records from the target based on array fetch size.
3.what autocorrect operates is it :compares a source row with a whole target and then it updates or discard the row. I know this is pushed down to a database level.
4. Can these two work together? Table Comparison with autocorrect on? icon_rolleyes.gif
5. We got some designs that do not use surrogate keys. There are duplicate rows in the Source. What if we use table comparison in this case without auto correct Load.
sorry about lot of questions. I searched tehcnical manuals but enough information is not available. Thanks a lot.
Back to top
Werner Daehn
Forum Devotee
Forum Devotee



Joined: 17 Dec 2004

speaker.gif*6
Posts: 10593
Location: Germany


flag
PostPosted: Mon Feb 06, 2006 10:51 am 
Post subject: Re: Autocorrect load or Table comparisons

Quote:
1.Are these two substitute for each other?

Yes, with TC having more options. You can also say TC is a superset. But if you do not require the additional options they are identical in respect to the endresult.

Quote:
2.Whether in Table Comparison(row by row method) it checks each source row with entire target table rows or only 1000 records from the target based on array fetch size.

TC will preparse the lookup like "select col1, col2 from comparetable where primary_key = :parameter:".
For each row it gets from the input it will execute that sql. As it is preparsed, no overhead, it is exactly what the database would do in case of a nested loop join as well. Only one difference: TC has to wait for the row, no parallel execution inside the transform. So if the database is far far far away and the network takes 1 sec to return the row, you will have a performance of 1 row per second. Typically not a problem and if, you can still use Degree of Parallelism (DOP). Fetch size does not play any role here.

Quote:
3.what autocorrect operates is it :compares a source row with a whole target and then it updates or discard the row. I know this is pushed down to a database level.

Execute the flow with just one input row and turn on the trace SQL loader feature. This will give you exactly the SQL statement autocorrect load does execute. In Oracle it is something like
Code:
select 'X' from table where primary_key = :param:;
update ..... where primary_key = :param:;
if no_data_found_exception
  insert ..... values.....;


So it does a select and if no data is found the no_data_exception is raised and the row will be inserted. Otherwise, updated.

And that's the point, autocorrect load does not compare, it updates. Always. Even if nothing changed.

Quote:
4. Can these two work together? Table Comparison with autocorrect on?

Yes, but then you will do the same thing twice. Actually, TC will send rows with insert/update OPCodes and the loader will ignore that entierly and run the autocorrect code. I can't see any value.

Quote:
5. We got some designs that do not use surrogate keys. There are duplicate rows in the Source. What if we use table comparison in this case without auto correct Load.

The autocorrect code is aware of duplicate rows. It will update the same row a couple of times. TC has a flag to deal with duplicate rows since DI 11(?).

Quote:
sorry about lot of questions. I searched tehcnical manuals but enough information is not available.


No prob.

_________________
Werner Daehn
ex Product Manager Data Services at SAP SE

www.rtdi.io
Back to top
RohitV
Forum Member
Forum Member



Joined: 21 Jun 2005

Posts: 10
Location: India


flag
PostPosted: Tue Feb 07, 2006 5:26 am 
Post subject: Re: Autocorrect load or Table comparisons

Werner Thank you so much for your expertise. This has answered all of my question. icon_lol.gif
The reply I got from Business Objects is:

Quote:
Table Comparison is not the same as Auto Correct Load. Table Comparison is a tool used to implement target-based changed-data capture. Auto Correct Load is a feature related to data flow recovery (in cases where a load does not complete because of connection problems, crashing, etc.). The features are not related. You use Table Comparison when you want to compare all rows in the source to potentially matching rows in the target, and insert/update/delete only the applicable rows. The chapter on "Techniques for Capturing Changed Data" in the Designer Guide contains more info on Table Comparison for target-based CDC. You use Auto Correct Load when you are running a job with recovery enabled. For more info on recoverable dataflows, please see the "Recovery Mechanisms" chapter in the DI Designer guide
.
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Data Integrator -> DI: Performance and Tuning  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0480 seconds using 17 queries. (SQL 0.0219 Parse 0.0009 Other 0.0253)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo