performance problem of asset selection statement.

[Former Member]

Hi guys quick question.

SELECT a~bukrs
           a~anln1
           b~anln2
           a~anlkl
           a~aktiv
           a~txt50
           a~lvtnr
           b~afabe
           b~afabg
           b~ndjar
           b~ndper
           INTO TABLE it_anla
           FROM anla AS a
           INNER JOIN anlb AS b
           ON a~bukrs EQ b~bukrs
           AND a~anln1 EQ b~anln1
           WHERE a~bukrs IN s_bukrs
           AND a~anln1 IN s_anln1
           AND a~anln2 EQ '0000'
           AND a~anlkl IN s_anlkl
           AND a~aktiv EQ '00000000'
           AND a~lvtnr IN s_lvtnr
           AND b~afabe EQ 01.

I have a select statement which is filtered by capitalization date which is the field AKTIV. This select alone has 450 thousand hits. Having an ANLN2 = 0 and AKTIV = blank/no date means that the asset group is no longer active.

Now for another scenario I have to retrieve table ANLA again excluding all the Main Asset Number/ANLN1 found on that table.

Is there a way to select by doing it only once?

I tried to pass all the anln1 to a range table but the program dumps. I think the range table can't handle too many entries.

Retrieving all the entries from the DB then processing it takes longer.

I tried to delete the table using a loop but it takes to long as it process the table every loop.

LOOP AT it_anla WHERE anln2 EQ '0000' AND aktiv EQ '0000000'.
      DELETE it_anla WHERE bukrs EQ it_anla-bukrs AND anln1 EQ it_anla-anln1.
    ENDLOOP.

Thanks.

Edited by: Thomas Zloch on Sep 21, 2010 5:39 PM - please use code tags

[Former Member]

Moderator message - Welcome to SCN If the range table for anln1 is large and contains distinct values, you can try using it in a FOR ALL ENTRIES construct rather than IN. Rob