Skip to Content
author's profile photo Former Member
Former Member

For all entries in XXXX works very slowly

Hello

I use below construction in update routine SAP BW 3.5

Table /bic/azmmo03bf00 is an active table of ods and contains 10mln records

table /bic/azmmo03bf00 is indexed by material and val_type

the size of internal table is 250K records

The entire contruction works very slowly and often hungs. Any suggestions how to improve it?

select material val_type /bic/zmmicpqua

into corresponding fields of table itab_odslookup3

from /bic/azmmo03bf00

for all entries in lt_pckge

where material eq lt_pckge-material

and val_type eq lt_pckge-val_type.

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Aug 11, 2009 at 07:49 AM

    Hi,

    1. LT_PCKGE should be sorted and duplicate records should be deleted

    Eg. SORT LT_PCKGE BY material val_type.

    DELETE ADJACENT DUPLICATES FROM lt_pckge

    COMPARING material val_type.

    IF NOT lt_pckge[] IS INITIAL.

    select material val_type /bic/zmmicpqua

    into table itab_odslookup3

    from /bic/azmmo03bf00

    for all entries in lt_pckge

    where material eq lt_pckge-material

    and val_type eq lt_pckge-val_type.

    ENDIF.

    2. Avoid using INTO CORRESPONDING FIELDS OF TABLE and use "INTO TABLE" in the SELECT statement

    3. If you believe there will be a huge volume of data will be retrieved from the Active table. Use CURSOR based SELECT

    Regards,

    Chathia.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 10, 2009 at 08:12 PM
    if not  lt_pckge[] is initial.
    sort  lt_pckge by material val_type.
    delete adjacent duplicates comparing material val_type.
    
    select material val_type /bic/zmmicpqua
    into corresponding fields of table itab_odslookup3
    from /bic/azmmo03bf00
    for all entries in lt_pckge
    where material eq lt_pckge-material
    and val_type eq lt_pckge-val_type.
    
    endif.
    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 11, 2009 at 09:10 AM

    Hi,

    >

    > The entire contruction works very slowly and often hungs. Any suggestions how to improve it?

    what does "very slowly and often hungs" mean? Could you post some execution details from ST05?

    Execution plan, records per exectuion, time per execution, avg. time per record, .... .

    As already posted by others: remove duplicates if there are some. Some fine tuning might be possible

    with the blocking factors (depends on comlexity of the sql statement).

    Kind regards,

    Hermann

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.