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.

Table comparison with "row-by-row select" method

Goto page 1, 2  Next
 
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
bodi_user
Forum Member
Forum Member



Joined: 17 Jan 2006

Posts: 23


flag
PostPosted: Tue Jan 31, 2006 11:50 am 
Post subject: Table comparison with "row-by-row select" method

My workflow is a initialization loading.
My source table has 1 000 000 rows, my target table 1 row and I use the "row-by-row select" method.

My issue : The process works in fine up to 450 000 rows (these rows are loaded in target table), then the virtual memory "seems" full (attachment) and the server CPU doesn't work any more.

Why my memory usage increase and increase whereas I use the "row-by-row select" method?

Thank you in advance.

Jérôme
Back to top
Werner Daehn
Forum Devotee
Forum Devotee



Joined: 17 Dec 2004

speaker.gif*6
Posts: 10593
Location: Germany


flag
PostPosted: Tue Jan 31, 2006 1:18 pm 
Post subject: Re: Table comparison with "row-by-row select" meth

Because tablecomparison is not your issue. There is something else causing the entire source data to be cached....
_________________
Werner Daehn
ex Product Manager Data Services at SAP SE

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



Joined: 17 Jan 2006

Posts: 23


flag
PostPosted: Wed Feb 01, 2006 3:46 am 
Post subject: Re: Table comparison with "row-by-row select" meth

My complete dataflow is :

Source (View) -> Query -> Table_Comp -> Map_Ope -> History_Pre -> Target (table)

Query : function key_generation (technical key), 2 lookup_ext (cache spec: NO_CACHE)
Table Cmp : row-by-row method
Map_Op : normal->normal, ins->ins, upd->upd, delete->discard

An other parameter (not in the dataflow) can be the cause of my issue?
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:37 am 
Post subject: Re: Table comparison with "row-by-row select" meth

The query: no distinct, group by, order by used?

When the flow started, please make a screenshot of the monitor log and post it here, that will give a first hint.

_________________
Werner Daehn
ex Product Manager Data Services at SAP SE

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



Joined: 17 Jan 2006

Posts: 23


flag
PostPosted: Thu Feb 02, 2006 3:53 am 
Post subject: Re: Table comparison with "row-by-row select" meth

Just a key_generation function, and 2 lookup_ext are used in the query.

Let me know what kind of trace do you want to see in the monitor log : Trace Session, WorkFlow, DataFlow...
Back to top
Werner Daehn
Forum Devotee
Forum Devotee



Joined: 17 Dec 2004

speaker.gif*6
Posts: 10593
Location: Germany


flag
PostPosted: Thu Feb 02, 2006 4:14 am 
Post subject: Re: Table comparison with "row-by-row select" meth

Not trace log, monitor log - the one with the row numbers.


I'd like to see the characteristics:

Is the first row count high and the target table still at 0? -> Yes, some query or other transform is blocking.

No, all row counts are about the same number? -> No transform is blocking.


Then, given the thread names and their row counts shown we can guess the position of the causing statement.


And if all does not help, I will ask you to execute the flow with the trace option "trace optimized flow" turned on. Then we can nail it down what AlView1 etc are and what the causing statement/function is.

_________________
Werner Daehn
ex Product Manager Data Services at SAP SE

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



Joined: 17 Jan 2006

Posts: 23


flag
PostPosted: Thu Feb 02, 2006 6:07 am 
Post subject: Re: Table comparison with "row-by-row select" meth

Apparently, no transform is blocking.

So, I will execute the flow with the trace option "trace optimized flow" turned on, and then post it (but after the lunch, I am too hungry icon_smile.gif )
Back to top
Werner Daehn
Forum Devotee
Forum Devotee



Joined: 17 Dec 2004

speaker.gif*6
Posts: 10593
Location: Germany


flag
PostPosted: Thu Feb 02, 2006 6:44 am 
Post subject: Re: Table comparison with "row-by-row select" meth

Agreed, no transform is blocking.

So the query does not block hence not consume memory.
TC is set to row-by-row does not consume memory.
History Preserving Transform never consumes any memory.
Lookups are all set to no_cache hence no memory either.
key_generation() function does not consume memory as well.

and memory is being consumed by al_engine.exe when executing this flow.

At least one of above statements has to be wrong. Please isolate.


On a different topic, do not use key_generation() function in the first query, drop that column entirely from the query as it will be added by TC anyway. And after the History Preserving add the Key Generation transform. With your current flow, you always create a new key, for new rows, for new versions of existing rows, but also for rows that are meant to be updated. Depending on the settings it is quite likely you end up with wrong results.

http://www.consulting-accounting.com/time/servlet/ShowPage?COMPANYID=43&ELEMENTID=1222

_________________
Werner Daehn
ex Product Manager Data Services at SAP SE

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



Joined: 17 Jan 2006

Posts: 23


flag
PostPosted: Thu Feb 02, 2006 11:23 am 
Post subject: Re: Table comparison with "row-by-row select" meth

wdaehn wrote:
Agreed, no transform is blocking.

So the query does not block hence not consume memory.
TC is set to row-by-row does not consume memory.
History Preserving Transform never consumes any memory.
Lookups are all set to no_cache hence no memory either.
key_generation() function does not consume memory as well.

and memory is being consumed by al_engine.exe when executing this flow.

At least one of above statements has to be wrong. Please isolate.



All these points are true icon_sad.gif I actually don't understand.

in attachment : Trace Optimized Flow
Back to top
Werner Daehn
Forum Devotee
Forum Devotee



Joined: 17 Dec 2004

speaker.gif*6
Posts: 10593
Location: Germany


flag
PostPosted: Thu Feb 02, 2006 12:35 pm 
Post subject: Re: Table comparison with "row-by-row select" meth

No cache split view, nothing.

What I have seen is a general memory increase and a monitor log. I haven't got a proof yet that the el_engine is accquiring the memory.

So make a screenshot of the process list while running the flow plus the trace log (I want to compare the al_engine process number of the trace log and the process list to get the proof)

_________________
Werner Daehn
ex Product Manager Data Services at SAP SE

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



Joined: 17 Jan 2006

Posts: 23


flag
PostPosted: Fri Feb 03, 2006 8:55 am 
Post subject: Re: Table comparison with "row-by-row select" meth

This is the sreenshots.

I see that I have 2 al_engine in the process list so I copy-paste the 2 screenshot log plus the process list.

PID process and PID log match.
Back to top
dnewton
Forum Addict
Forum Addict



Joined: 30 Jan 2004
ASUG Icon
Posts: 3458
Location: Massachusetts, USA


flag
PostPosted: Sat Feb 04, 2006 8:27 am 
Post subject: Re: Table comparison with "row-by-row select" meth

Are you sure your target table is indexed appropriately? That is important for a table compare operation.
Back to top
bodi_user
Forum Member
Forum Member



Joined: 17 Jan 2006

Posts: 23


flag
PostPosted: Mon Feb 06, 2006 3:48 am 
Post subject: Re: Table comparison with "row-by-row select" meth

My target table is indexed on the ID which is my "Input primary key columns" on the table comparaison.
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 4:39 am 
Post subject: Re: Table comparison with "row-by-row select" meth

please post the ATL for this job (or file a bug telling support that I looked into it and did not found the root cause).
_________________
Werner Daehn
ex Product Manager Data Services at SAP SE

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



Joined: 17 Jan 2006

Posts: 23


flag
PostPosted: Thu Feb 09, 2006 9:08 am 
Post subject: Re: Table comparison with "row-by-row select" meth

The source of my issue :

In Table Comparison, if the checkbox "Input contains duplicate keys" is validated, the table of comparison (=target table) is loaded in cache memory even if the "row by row select" method is chosen.

Thank you for your help.


Last edited by bodi_user on Thu Feb 09, 2006 9:26 am, edited 1 time in total
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 2 All times are GMT - 5 Hours
Goto page 1, 2  Next
 
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.0304 seconds using 17 queries. (SQL 0.0030 Parse 0.0010 Other 0.0265)
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