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

Using Ranges instead of For all entries..

Hi All,

I have a two select quires which is taking a long time on the Production server. the query is as follows:-

select distinct addrnum ktext into table i_t499s

from t499s

for all entries in i_anlz

where werks = i_anlz-werks

and stand = i_anlz-stort.

if not i_t499s[] is initial.

delete i_t499s where adrnr is initial.

sort i_t499s ascending by adrnr.

endif.

endif.

select distinct addrnumber street city1 city2

country region post_code1 taxjurcode

into table i_adrc

from adrc

for all entries in i_t499s

where addrnumber = i_t499s-adrnr

and date_from <= sy-datum

and date_to >= sy-datum

and country = 'US'.

Will the usage of the Selection options RANGES i both the above quires improve the performance ???

Add a comment
10|10000 characters needed characters exceeded

Related questions

4 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Jan 04, 2008 at 01:55 PM

    Try using the following code.

    DATA: i_anlz_tmp LIKE TABLE OF i_anlz.

    IF NOT i_anlz[] IS INITIAL.

    i_anlz_tmp[] = i_anlz[].

    SORT i_anlz_tmp BY werks stort.

    DELETE ADJACENT DUPLICATES FROM i_anlz_tmp

    COMPARING werks stort.

    SELECT addrnum

    ktext

    FROM t499s

    INTO TABLE i_t499s

    FOR ALL ENTRIES IN i_anlz_tmp

    WHERE werks EQ i_anlz_tmp-werks

    AND stand EQ i_anlz_tmp-stort.

    IF sy-subrc EQ 0.

    DELETE i_t499s WHERE adrnr IS INITIAL.

    IF NOT i_t499s[] IS INITIAL.

    SORT i_t499s BY adrnr.

    DELETE ADJACENT DUPLICATES FROM i_t499s COMPARING adrnr.

    SELECT addrnumber

    street

    city1

    city2

    country

    region

    post_code1

    taxjurcode

    FROM adrc

    INTO TABLE i_adrc

    FOR ALL ENTRIES IN i_t499s

    WHERE addrnumber EQ i_t499s-adrnr

    AND date_from LE sy-datum

    AND date_to GE sy-datum

    AND country EQ 'US'.

    IF sy-subrc EQ 0.

    SORT i_adrc BY addrnumber.

    DELETE ADJACENT DUPLICATES FROM i_adrc COMPARING addrnumber.

    ENDIF.

    ENDIF.

    ENDIF.

    ELSE.

    REFRESH: i_t499s,

    i_adrc .

    ENDIF.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 04, 2008 at 03:25 PM

    First porblem, there is absolutely no need for the distinct in the select as you specify both key fields in the select.

    The dstinct prevents only the usage of the table buffer and table t499s is fully buffered! That is the reason why I would not recommend a join.

    select *

    into table i_t499s

    from t499s

    for all entries in i_anlz

    where werks = i_anlz-werks

    and stand = i_anlz-stort.

    if not i_t499s[] is initial.

    delete i_t499s where adrnr is initial.

    endif.

    Similar in the other select, how should it be possible that

    several addrnumbers come back:

    addrnumber, date_from and nation are the unique key

    Very often there is only one addrnumber.

    I can not see a possibility for a performance problem.

    Please run several time and check with SQL trace.

    /people/siegfried.boes/blog/2007/09/05/the-sql-trace-st05-150-quick-and-easy

    Check time per record to see processing is fast.

    Check number of records to see whether you do a lot, which will of course need some time.

    Ranges are not recommended, as they will not work for large tables i_t499s

    Siegfried

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 07, 2008 at 03:45 PM

    See if this helps:

    CHECK NOT i_anlz[] IS INITIAL.         "<=== New
    
    SELECT addrnum ktext INTO TABLE i_t499s
      FROM t499s
      FOR ALL ENTRIES IN i_anlz
      WHERE werks = i_anlz-werks
        AND stand = i_anlz-stort.
    
    IF NOT i_t499s[] IS INITIAL.
      DELETE i_t499s WHERE adrnr IS INITIAL.
      SORT i_t499s ASCENDING BY adrnr.
    
      SELECT DISTINCT addrnumber street city1 city2
             country region post_code1 taxjurcode
        INTO TABLE i_adrc
        FROM adrc
        FOR ALL ENTRIES IN i_t499s
        WHERE addrnumber = i_t499s-adrnr
          AND date_from <= sy-datum
          AND date_to   >= sy-datum
          AND country = 'US'.
    ENDIF.                              "<===  Moved

    Rob

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 08, 2008 at 05:07 AM

    Hi Shilpik,

    Select Options will not improve performance... In fact it can take more time than FOR ALL ENTRIES if records are more. (Unles you use option of Inclusive Between and your data contain more ranges of number rather than discrete values).

    Regards,

    Mohaiyuddin

    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.