Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Using Ranges instead of For all entries..

Former Member
0 Kudos
100

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 ???

4 REPLIES 4

Former Member
0 Kudos
63

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.

former_member194613
Active Contributor
0 Kudos
63

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

Former Member
0 Kudos
63

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

Former Member
0 Kudos
63

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