LOOP....ENDLOOP and SELECT Statement Issue

[Former Member]

Hi All,

Following is the query.

*BSIK
SELECT A~LIFNR A~BELNR A~XBLNR A~BLART A~BUDAT A~GJAHR A~SHKZG A~DMBTR 
A~WRBTR A~DMBT1 A~DMBT2 B~NAME1 INTO CORRESPONDING FIELDS OF TABLE ITAB 
FROM BSIK AS A INNER JOIN LFA1 AS B ON A~LIFNR EQ B~LIFNR WHERE BUKRS EQ 
P_BUKRS AND BUDAT IN S_BUDAT AND A~LIFNR IN S_LIFNR AND A~BELNR IN S_BELNR 
AND A~BLART IN S_BLART.
*BSAK
SELECT A~LIFNR A~BELNR A~XBLNR A~BLART A~BUDAT A~GJAHR A~SHKZG A~DMBTR 
A~WRBTR A~DMBT1 A~DMBT2 B~NAME1 APPENDING CORRESPONDING FIELDS OF TABLE ITAB 
FROM BSAK AS A INNER JOIN LFA1 AS B ON A~LIFNR EQ B~LIFNR WHERE BUKRS EQ 
P_BUKRS AND BUDAT IN S_BUDAT AND A~LIFNR IN S_LIFNR AND A~BELNR IN S_BELNR 
AND A~BLART IN S_BLART.
"Here my ITAB has 465322 records.
LOOP AT ITAB.
SELECT SINGLE AWKEY INTO G_AWKEY FROM BKPF CLIENT SPECIFIED WHERE MANDT EQ SY-MANDT 
AND BUKRS EQ P_BUKRS AND BELNR EQ ITAB-BELNR AND GJAHR EQ ITAB-GJAHR.
IF SY_SUBRC = 0.
SELECT SINGLE EBELN INTO G_EBELN FROM RSEG CLIENT SPECIFIED WHERE MANDT EQ SY-MANDT 
AND BELNR EQ G_AWKEY(10) AND GJAHR EQ ITAB-GJAHR AND EBELN IN S_EBELN.
        IF NOT G_EBELN IS INITIAL.
        ITAB-EBELN = G_EBELN.
        MODIFY ITAB.
        ELSE.
        DELETE ITAB INDEX SY-TABIX.
        ENDIF.
ELSE.
DELETE ITAB INDEX SY_TABIX.
ENDIF.
ENDLOOP.

Here my LOOP......ENDLOOP is taking too much time (15 minutes). Can anybody suggest me what to do in this case. I have also tried FOR ALL ENTRIES. It is also taking lot of time. This is my first time I am working with FI data and Tables.I have also searched GOOGLE. But no use.Please suggest is there any other way to achieve this.

Thanks.

[Former Member]

Hi Nani

You should do your data selection one time and out of Loop EndLopp

Try with this

IF  NOT ITAB[]  IS INITIAL

SELECT *  
INTO  TABLE  WT_BKPF
FROM BKPF CLIENT SPECIFIED 
FOR ALL ENTRIES IN ITAB
WHERE MANDT EQ SY-MANDT 
    AND BUKRS EQ P_BUKRS 
    AND BELNR EQ ITAB-BELNR 
   AND GJAHR EQ ITAB-GJAHR.

IF SY-SUBRC EQ 0.

SORT  WT_BKPF BY BELNR GJAHR . 

SELECT  * 
   INTO TABLE  WT_RSEG
FROM RSEG CLIENT SPECIFIED 
FOR ALL ENTRIES IN  ITAB 
   WHERE MANDT EQ SY-MANDT 
    AND GJAHR EQ ITAB-GJAHR 
    AND EBELN IN S_EBELN.

IF SY-SUBRC EQ 0.

   SORT  WT_RSEG  BY BELNR GJAHR  EBELN . 

ENDIF.


ENDIF.

LOOP AT ITAB

   READ TABLE  WT_BKPF  
   WITH KEY   BELNR =   ITAB-BELNR
                   GJAHR  =   ITAB-GJAHR
    BINARY SERACH.

  IF SY-SUBRC EQ 0.

    READ TABLE  WT_RSEG
     WITH KEY   BELNR =   ITAB-BELNR
                       GJAHR  =   ITAB-GJAHR
                      BELNR = WT_BKPF -G_AWKEY(10)
        BINARY SERACH.

  IF SY-SUBRC EQ 0.

        IF NOT    WT_RSEG-EBELN IS INITIAL.
        
            ITAB-EBELN =   WT_RSEG-EBELN.
            MODIFY ITAB.
    
         ENDIF.


  ELSE.

        DELETE ITAB INDEX SY-TABIX.
  ENDIF.


ELSE.


DELETE ITAB INDEX SY_TABIX.

ENDIF.
.


ENDLOOP

ENDIF.

Best Regards,

Hamza KOUACHI

[Former Member]

Hi Hamza,

Thanks for the reply. Can you also tell me how to declare WT_BKPF and WT_RSEG?

Thanks.

[Former Member]

Hi Nani,

there are more way to declare it

You can use this

DATA: WT_BKPF TYPE bkpf WITH HEADER LINE.

DATA: WT_RSEG TYPE rseg WITH HEADER LINE.

you can also declare TYPES with only fields wich you need

Best Regards,

Hamza KOUACHI

[Former Member]

Hi Hamza,

I have declared the internal tables as you said. But my select * from RSEG is taking too much time. I can say it is forever thru that select * statement.

I think it's a performance issue. Any suggestions?

[Former Member]

As a rule NEVER do a SELECT *, unless you really want all the records which is very unlikely. And also use key fields in where conditions and remove MANDT field from your query.

As discussed earlier you <FS> instead of Internal Tables and Work area to get better result because this uses reference (memory address) hence to and fro is quicker.

Thank you!

Reetesh

[Former Member]

Hi ,

Use delete adjacent duplicates command like mentioned below.

itab2 = itab.

delete ADJACENT DUPLICATES FROM itab2 COMPARING gjahr.
SELECT  * 
   INTO TABLE  WT_RSEG
   FROM RSEG CLIENT SPECIFIED 
   FOR ALL ENTRIES IN  ITAB2 
          WHERE MANDT EQ SY-MANDT 
          AND GJAHR EQ ITAB2-GJAHR 
          AND EBELN IN S_EBELN.

Thanks,

Vijay

[Former Member]

As a rule NEVER do a SELECT *, unless you really want all the records which is very unlikely. And also use key fields in where conditions and remove MANDT field from your query.

SELECT * does not return all the records. It simply returns all of the fields of a single record. It is unlikely that this would be a big problem.

Rob

[Former Member]

oops, well that is what I meant to say, may be it was not clear, enough! Though what is clear is when you look to optimize your query you should not use '*' as the best thing you can do.

Reetesh

[Former Member]

Hi,

Also, make a field symbol of your internal table and work area type that will considerably improve the performance.

Please go thru the Document: http://help.sap.com/saphelp_nw04/helpdata/en/fc/eb387a358411d1829f0000e829fbfe/content.htm

Hope this helps you!

Reetesh

[Former Member]

Hi Reetesh,

Thanks for the reply. Can you please give me an example/example link instead of sap help link.

Thanks.

[Former Member]

Here you go:

http://help-abap.zevolving.com/2009/12/use-of-field-symbols-vs-work-area/

http://sample-code-abap.blogspot.com/2008/07/sap-abap-dynamic-internal-table-and.html

Reetesh

[Former Member]

Nani - have you run transaction ST05 to see where the problem is?

Rob

[Former Member]

Hi Rob,

Thanks for the reply.

I have run SE30 and not able to get thru the statement.

SELECT  belnr gjahr ebeln  INTO TABLE  WT_RSEG
FROM RSEG FOR ALL ENTRIES IN  ITAB 
   WHERE GJAHR EQ ITAB-GJAHR 
       AND EBELN IN S_EBELN.

I think my WHERE condition is not satisfied because I have not

specified BELNR in my WHERE condition. I tried specifying

BELNR in WHERE condition as below.

SELECT  belnr gjahr ebeln  INTO TABLE  WT_RSEG
FROM RSEG FOR ALL ENTRIES IN ITAB 
   WHERE belnr eq itab-awkey     "I have also tried belnr eq itab-awkey+0(10)
       AND GJAHR EQ ITAB-GJAHR 
       AND EBELN IN S_EBELN.

But it is giving me an error saying that "When you use the addition "FOR ALL ENTRIES IN itab" the fields "BELNR" and "ITAB_AWKEY" must have the same type and same length.

How do I need to declare my BELNR/AWKEY. Please suggest.

Thanks.

[Former Member]

hi,

or check the LDB ( Logical data base which suits for your requirement)

[Former Member]

Hi All,

This is how I have modified my code.

*BSIK
SELECT A~LIFNR A~BELNR A~XBLNR A~BLART A~BUDAT A~GJAHR A~SHKZG A~DMBTR 
A~WRBTR A~DMBT1 A~DMBT2 B~NAME1 INTO CORRESPONDING FIELDS OF TABLE ITAB 
FROM BSIK AS A INNER JOIN LFA1 AS B ON A~LIFNR EQ B~LIFNR WHERE BUKRS EQ 
P_BUKRS AND BUDAT IN S_BUDAT AND A~LIFNR IN S_LIFNR AND A~BELNR IN S_BELNR 
AND A~BLART IN S_BLART.
*BSAK
SELECT A~LIFNR A~BELNR A~XBLNR A~BLART A~BUDAT A~GJAHR A~SHKZG A~DMBTR 
A~WRBTR A~DMBT1 A~DMBT2 B~NAME1 APPENDING CORRESPONDING FIELDS OF TABLE ITAB 
FROM BSAK AS A INNER JOIN LFA1 AS B ON A~LIFNR EQ B~LIFNR WHERE BUKRS EQ 
P_BUKRS AND BUDAT IN S_BUDAT AND A~LIFNR IN S_LIFNR AND A~BELNR IN S_BELNR 
AND A~BLART IN S_BLART.

IF NOT ITAB[] IS INITIAL.
    SELECT BELNR GJAHR AWKEY INTO TABLE IT_BKPF
                             FROM BKPF FOR ALL ENTRIES IN ITAB
                             WHERE BUKRS EQ P_BUKRS
                               AND BELNR EQ ITAB-BELNR
                               AND GJAHR EQ ITAB-GJAHR.
    IF SY-SUBRC EQ 0.
      SORT IT_BKPF BY BELNR GJAHR.
 "This select statement is taking forever. (performance issue)
      SELECT BELNR GJAHR EBELN INTO TABLE IT_RSEG   
                               FROM RSEG FOR ALL ENTRIES IN ITAB
                               WHERE GJAHR EQ ITAB-GJAHR
                                 AND EBELN IN S_EBELN.
      IF SY-SUBRC EQ 0.
        SORT IT_RSEG BY BELNR GJAHR EBELN.
      ENDIF.
    ENDIF.
    LOOP AT ITAB.
      READ TABLE IT_BKPF INTO WA_BKPF WITH KEY BELNR =   ITAB-BELNR
                GJAHR =   ITAB-GJAHR BINARY SEARCH.
      IF SY-SUBRC EQ 0.
        READ TABLE IT_RSEG INTO WA_RSEG WITH KEY BELNR =   ITAB-BELNR
                   GJAHR =   ITAB-GJAHR BINARY SEARCH.
* BELNR = WA_BKPF-AWKEY(10) BINARY SEARCH.
                      "I am getting error-Cant pass BELNR two times
        IF SY-SUBRC EQ 0.
          IF NOT WA_RSEG-EBELN IS INITIAL.
            ITAB-EBELN =   WA_RSEG-EBELN.
            MODIFY ITAB.
          ENDIF.
        ELSE.
          DELETE ITAB INDEX SY-TABIX.
        ENDIF.
      ELSE.
        DELETE ITAB INDEX SY-TABIX.
      ENDIF.
    ENDLOOP.
  ENDIF.

Any suggestions. Please help. Examples are really appreciated.Also let me know if there is any other way to achieve this. I have not worked with Field symbols earlier. If using FIELD SYMBOLS solves the issues please let me know how do I need to use them in my case.

Thanks.

Edited by: Rob Burbank on Nov 10, 2011 10:17 AM

[yuri_ziryukin]

Well, Nani, you have made many mistakes in your report.

1) When selecting data from BSIK and BSAK you get over 400.000 entries. If you look at them, they are "item-based". So for each accounting document you'll have many items in your ITAB.

Your next SELECT statement from BKPF is using only document header.

So, this is your first mistake.

So if you really need to prove the existence of the corresponding entries in BKPF, you should copy ITAB to another table with the same structure, sort it by BELNR, GJAHR and delete adjacent duplicates before making a select from BKPF:

ITAB2[] = ITAB[],
sort ITAB2 by BELNR GJAHR.
delete adjacent duplicates from ITAB2 comparing BELNR GJAHR.

2) Why do you need a check against BKPF? If I understand the table structire correctly, BSIK and BSAK are "index" tables. From my understanding there will ALWAYS be an entry in BKPF for the corresponding enries in BSAK and BSIK. Otherwise it's a data inconsistency issue which needs to be resolved.

If my understanding is correct, the select from BKPF and subsequent READ TABLE inside the loop should be removed.

3) Your select from RSEG is completely useless in a way you have it now.

I guess you need to change it like this (keep in mind that you need to implement point 1 before this select):

IF ITAB2[] IS NOT INITIAL.

SELECT BELNR GJAHR EBELN INTO TABLE IT_RSEG

FROM RSEG FOR ALL ENTRIES IN ITAB2

WHERE BELNR EQ ITAB2-BELNR AND

GJAHR EQ ITAB2-GJAHR

AND EBELN IN S_EBELN.

ENDIF.

4) Use field symbols for ITAB and IT_RSEG.

Adjust your coding:

field-symbols: <fs_itab> like line of itab,
             <fs_rseg> like line of it_rseg.

LOOP AT ITAB assigning <fs_itab>.
* Think about removing the below check completely! See my point 2.
      *READ TABLE IT_BKPF INTO WA_BKPF WITH KEY BELNR =   ITAB-BELNR*
                *GJAHR =   ITAB-GJAHR BINARY SEARCH.*
      IF SY-SUBRC EQ 0.
        READ TABLE IT_RSEG ASSIGNING <FS_RSEG> WITH KEY BELNR = <FS_ITAB>-BELNR
                   GJAHR =  <FS_ITAB>-GJAHR BINARY SEARCH.
        IF SY-SUBRC EQ 0.
          IF NOT <FS_RSEG>-EBELN IS INITIAL.
            <FS_ITAB>-EBELN = <FS_RSEG>-EBELN.
*            MODIFY ITAB. "not needed anymore
          ENDIF.
        ELSE.
          DELETE ITAB. "index specification not required in loop
        ENDIF.
      ELSE.
        DELETE ITAB. "index specification not required in loop
      ENDIF.
    ENDLOOP.

Regards,

Yuri

[Former Member]

I have made changes and added proper comments for every change

Please use below code:

SELECT A~LIFNR
       A~BELNR
       A~XBLNR
       A~BLART
       A~BUDAT
       A~GJAHR
       A~SHKZG
       A~DMBTR
       A~WRBTR
       A~DMBT1
       A~DMBT2
       B~NAME1
*  INTO CORRESPONDING FIELDS OF TABLE ITAB
*I think u have declared itab with above field plus AWKEY & EBELN.
*Declare itab with these fields at the top and then AWKEY & EBELN.
  INTO TABLE ITAB
  FROM BSIK AS A
 INNER JOIN LFA1 AS B ON A~LIFNR EQ B~LIFNR
 WHERE BUKRS EQ P_BUKRS
   AND BUDAT IN S_BUDAT
   AND A~LIFNR IN S_LIFNR
   AND A~BELNR IN S_BELNR 
   AND A~BLART IN S_BLART.

*BSAK
SELECT A~LIFNR
       A~BELNR
       A~XBLNR
       A~BLART
       A~BUDAT
       A~GJAHR
       A~SHKZG
       A~DMBTR
       A~WRBTR
       A~DMBT1
       A~DMBT2
       B~NAME1
*APPENDING CORRESPONDING FIELDS OF TABLE ITAB
APPENDING TABLE ITAB
  FROM BSAK AS A
 INNER JOIN LFA1 AS B ON A~LIFNR EQ B~LIFNR
 WHERE BUKRS EQ P_BUKRS
   AND BUDAT IN S_BUDAT
   AND A~LIFNR IN S_LIFNR
   AND A~BELNR IN S_BELNR
   AND A~BLART IN S_BLART.
"Here my ITAB has 465322 records.

DATA: itab_1 LIKE ITAB.

itab_1 = ITAB[].
SORT itab_1 BY BUKRS BELNR GJAHR.
DELETE ADJACENT DUPLICATES FROM itab_1 COMPARING BUKRS BELNR GJAHR.

IF itab_1[] IS NOT INITIAL.
  SELECT BUKRS
         BELNR
         GJAHR
         AWKEY
*Define table it_bkpf with above four fields
    INTO TABLE it_bkpf
    FROM BKPF "CLIENT SPECIFIED
* WHERE MANDT EQ SY-MANDT
*U can remove client specified since ur using SY-MANDT
*It has same effect when not specified
     FOR ALL ENTREIS IN itab_1
   WHERE BUKRS EQ P_BUKRS
     AND BELNR EQ itab_1-BELNR
     AND GJAHR EQ itab_1-GJAHR.
ENDIF.

REFRESH itab_1[].
*Declare itab_2 same as it_bkpf[]
itab_2 = it_bkpf[].
SORT itab_2 BY AWKEY GJAHR.
DELETE ADJACENT DUPLICATES FROM itab_2 COMPARING AWKEY GJAHR.

IF itab_2[] IS NOT INITIAL.
  SELECT BELNR
         GJAHR
         EBELN
    INTO TABLE it_rseg
    FROM RSEG "CLIENT SPECIFIED
*   WHERE MANDT EQ SY-MANDT
     FOR ALL ENTREIS IN itab_2
   WHERE BELNR EQ itab_2-awkey+0(10)
     AND GJAHR EQ itab_2-GJAHR
     AND EBELN IN S_EBELN.
  IF SY-SUBRC = 0
    SORT it_rseg BY BELNR GJAHR.
  ENDIF.
ENDIF.

FIELD-SYMBOLS: <wa_itab> LIKE LINE OF ITAB.
DATA: lv_tabix TYPE SY-TABIX.


LOOP AT ITAB ASSIGNING <wa_itab>.
  lv_tabix = SY-TABIX.
  READ TABLE it_bkpf INTO wa_bkpf
                     WITH KEY BUKRS = <wa_itab>-BUKRS
                              BELNR = <wa_itab>-BELNR
                              GJAHR = <wa_itab>-GJAHR
                     BINARY SEARCH.
  IF SY_SUBRC = 0.
    READ TABLE it_rseg INTO wa_rseg
                       WITH KEY BELNR = wa_bkpf-awkey+0(10)
                                GJAHR = wa_bkpf-gjahr
                       BINARY SERACH.
    IF SY-SUBRC = 0.
      <wa_itab>-EBELN = wa_rseg-ebeln.
*      MODIFY ITAB.
*Not required to modify as table entry is assigned to FS.
    ELSE.
      DELETE ITAB FROM <wa_itab> INDEX lv_tabix.
    ENDIF.
  ELSE.
    DELETE ITAB FROM <wa_itab> INDEX lv_tabix.
  ENDIF.
ENDLOOP.

[Former Member]

Hi,

Quite strange that nobody pointed out some of the performance standards: Use SORTED tables with unique keys (Removes any SORT command). DO NOT use statement DELETE inside your LOOP. Instead use a DELETE WHERE outside...

Kr,

Manu.

[yuri_ziryukin]

Hi,
> 
1) Use SORTED tables with unique keys (Removes any SORT command).
2) DO NOT use statement DELETE inside your LOOP. Instead use a DELETE WHERE outside...
> 
> Kr,
> Manu.

1) Yes, sorted table would be an option, but since when using sorted tables and moreover UNIQUE keys became a STANDARD? And I would like to see a proof link.

2) Really? Why? And please show me the coding sample to delete all entries from ITAB1 where the corresponding entry does not exist in ITAB2 using DELETE WHERE outside of the loop.

[Former Member]

1) Sorted table would prevent the use of an inefficient SORT command on such big table, which is quite enough to avoid using standard one

2) Well, I think this is quite obvious that 1 DELETE is more efficient that many nested DELETE...

This could be done easily by maintaining a flag on itab instead of statement DELETE in your example.

here you are:

LOOP AT ITAB assigning <fs_itab>.
* Think about removing the below check completely! See my point 2.
      *READ TABLE IT_BKPF INTO WA_BKPF WITH KEY BELNR =   ITAB-BELNR*
                *GJAHR =   ITAB-GJAHR BINARY SEARCH.*
      IF SY-SUBRC EQ 0.
        READ TABLE IT_RSEG ASSIGNING <FS_RSEG> WITH KEY BELNR = <FS_ITAB>-BELNR
                   GJAHR =  <FS_ITAB>-GJAHR BINARY SEARCH.
        IF SY-SUBRC EQ 0.
          IF NOT <FS_RSEG>-EBELN IS INITIAL.
            <FS_ITAB>-EBELN = <FS_RSEG>-EBELN.
*            MODIFY ITAB. "not needed anymore
          ENDIF.
        ELSE.
          "DELETE ITAB. "index specification not required in loop
          <fs_itab>-del = 'X'.
        ENDIF.
      ELSE.
        "DELETE ITAB. "index specification not required in loop
         <fs_itab>-del = 'X'.
      ENDIF.
ENDLOOP.
DELETE itab WHERE del = 'X'.

Kr,

Manu.

Edited by: Manu D'Haeyer on Nov 10, 2011 10:58 AM

[yuri_ziryukin]

1) Sorted table would prevent the use of an inefficient SORT command on such big table, which is quite enough to avoid using standard one
> 2) Well, I think this is quite obvious that 1 DELETE is more efficient that 465322 nested DELETE... 
> This could be done easily by maintaining a flag on itab instead of statement DELETE in your example.
> 
> here you are:
> 

> LOOP AT ITAB assigning <fs_itab>.
> * Think about removing the below check completely! See my point 2.
>       *READ TABLE IT_BKPF INTO WA_BKPF WITH KEY BELNR =   ITAB-BELNR*
>                 *GJAHR =   ITAB-GJAHR BINARY SEARCH.*
>       IF SY-SUBRC EQ 0.
>         READ TABLE IT_RSEG ASSIGNING <FS_RSEG> WITH KEY BELNR = <FS_ITAB>-BELNR
>                    GJAHR =  <FS_ITAB>-GJAHR BINARY SEARCH.
>         IF SY-SUBRC EQ 0.
>           IF NOT <FS_RSEG>-EBELN IS INITIAL.
>             <FS_ITAB>-EBELN = <FS_RSEG>-EBELN.
> *            MODIFY ITAB. "not needed anymore
>           ENDIF.
>         ELSE.
>           "DELETE ITAB. "index specification not required in loop
>           <fs_itab>-del = 'X'.
>         ENDIF.
>       ELSE.
>         "DELETE ITAB. "index specification not required in loop
>          <fs_itab>-del = 'X'.
>       ENDIF.
> ENDLOOP.
> DELETE itab WHERE del = 'X'.
> 
> 
> Kr,
> Manu.
> 
> Edited by: Manu D'Haeyer on Nov 10, 2011 10:58 AM

1) When selecting from database table into an internal sorted table, sort HAVE to be performed anyway. Who told you that the SORT command is inefficient, escpecially executed only once?

2) I am going to test both coding samples in internal test system. Really looking forward to see which one is faster...

I'll come back in 30 min.

[Former Member]

Hi,

1) When selecting from database table into an internal sorted table, sort HAVE to be performed anyway. Who told you that the SORT command is inefficient, escpecially executed only once?

My little finger

From SAP help:

Sorted tables

This is the most appropriate type if you need a table which is sorted as you fill it. You fill sorted tables using the INSERT statement. Entries are inserted according to the sort sequence defined through the table key. Any illegal entries are recognized as soon as you try to add them to the table. The response time for key access is logarithmically proportional to the number of table entries, since the system always uses a binary search. Sorted tables are particularly useful for partially sequential processing in a LOOP if you specify the beginning of the table key in the WHERE condition.

OK, let say a single sortstandard tablebinary search is quite the same.... I would say why bother, I usually always use SORTED table when I need sorted data... I mean I find this quite logical...

2) I am going to test both coding samples in internal test system. Really looking forward to see which one is faster...

Be my guest.... and don't forget to use an itab with more than 400 000 entries

Kr,

Manu.

[SuhaSaha]

Hello Gentlemen,

My 2 cents ...

I prefer using SORTED TABLEs over STANDARD TABLEs because:

1. You don't need to explicitly specify the BINARY SEARCH addition while READ'ing them. And if the key field sequence used in READ'ing is different from the table key, you get an Code Inspector error.

In case of STANDARD TABLEs, no such error is raised if you use a BINARY SEARCH addition w/o SORT'ing the table.

SORTED v/s STANDARD: 1-0.

2. LOOP...WHERE & DELETE...WHERE statements are optimised for SORTED TABLEs if the WHERE condition contains the key-fields(left-justified). Not the case with STANDARD TABLEs.

SORTED v/s STANDARD: 2-0.

@Manu:

Well, I think this is quite obvious that 1 DELETE is more efficient that 465322 nested DELETE... 
> This could be done easily by maintaining a flag on itab instead of statement DELETE in your example.

Actually i don't think so!

DELETE itab WHERE del = 'X'.

In this case a sequential table read is performed on the table i.e., SAP runtime checks all the records of the internal table to see if del = 'X'.

Generally DELETE inside LOOPs is not recommended because the SY-TABIX is messed up during the deletion. And if you're performing any operations based on Sy-TABIX it might give erroneous results.

I'll be happy to hear your thoughts.

BR,

Suhas

[yuri_ziryukin]

So 1) is basically the same as using a standard table and sort+binary search.

I know, it's a good option to use a sorted table, but I don't like when somebody tells that the other option is "taboo" and should not be used.

2) Strangely (although I did not expect it), both options (delete in loop and delete after loop) show same runtime for a table ITAB1 with 100.000 entries.

I'll do some more testing after lunch.

[Former Member]

I don't like when somebody tells that the other option is "taboo" and should not be used.

Never said such.... you seem to miss some confidence in yourself

2) Totally agree with Suhas. This is the best answer to why avoid using DELETE within LOOP.... Altough I'm quite surpised that both option give the same performence result...

Kr,

Manu.

[yuri_ziryukin]

Delete outside of the loop.

Form SORT_DELETE	1	279.914	=	279.914
Sort LT_ITEM	1	4.442	=	4.442
Loop at LT_ORDER	1	201.074		75.579
Read Table LT_ITEM	99.205	125.495	=	125.495
Delete LT_ORDER	1	60.087	=	60.087

Delete inside the loop

Form SORT_DELETE	1	282.296	=	282.296
Sort LT_ITEM	1	4.681	=	4.681
Loop at LT_ORDER	1	277.597		101.928
Read Table LT_ITEM	99.205	129.088	=	129.088
Delete LT_ORDER	38.170	46.581	=	46.581

What surprises me is the net time of the LOOP itself. In case of the DELETE inside the loop, the net time of the LOOP statement increased by 25%. I am afraid that it might be the result of the ABAP trace overhead playing its role here.

Here is the result w/o the "internal tables" option in ABAP trace:

Delete outside of the loop:

Perform SORT_DELETE 1 144.706 = 144.706

Delete inside the loop:

Form SORT_DELETE 1 134.970 = 134.970

The difference is not significant.

Testing with 400.000 entries in the ITAB1...

Delete outside of the loop:

Perform SORT_DELETE 1 499.793 = 499.793

Delete inside the loop:

Perform SORT_DELETE 1 500.576 = 500.576

Basically no difference at all.

So, the practical example shows that both options provide same performance.

Now concerning sy-tabix.

Sy-tabix is changed by the previous READ TABLE statement so it cannot be used for any operations anymore.

The DELETE statement itself does not change sy-tabix, tested in debugger.

To summarize: from my point of view, adding one more field to the structure means additional programming effort if the data type is used from the Data Dictionary (often the case). So I, persomally, would prefer the option with the delete inside the loop simply because I have to program less. Thus, I cannot consider your statement "DO NOT use statement DELETE inside your LOOP. Instead use a DELETE WHERE outside" a valid recommendation.

Edited by: Yuri Ziryukin on Nov 10, 2011 1:14 PM

[Former Member]

... Please be aware that with performance there is no 'do always' and 'do never', it always depends.

You can get the same performance with standard tables as with sorted tables (even slightly faster, because the check for the key ís not done). However, the sorted tables are much more convenient and fault-tolerant, the optimization of the READ with a BINARY SEARCH is o.k., but the optimization of the LOOP WHERE with READ, LOOP FROm INDEX, EXIT is very cumbersome.

So use sorted tables whenever possible. Actually the sorted table does not need a unique table key.

But be aware of the possibilites how to optimize standard tables.

Use ASSIGNING with LOOPs and definitely with large or nested line structures (=wa).

But the READ will not profit much from the ASSIGNING if the table is narrow.

Try to avoid header lines, better use explicit work areas.

And a DELETE with INDEX inside a LOOP is as good as a DELETE WHERE. (For the analysis it is even better as the handling of the DELETE in the SE30 is weird).

Ue FAE instead SELECTs inside LOOPs:

+ you must take care that the driver table is not empty.

+ if there is a chance that the driver table contains duplicates (only for the fields of the WHERE-clause) then delete them either with SORT and DELETE ADJACENT DUPLICATES or with a COLLECT of the required key fields into a hashed table with unique key.

Do not use SELECT * for wide tables, select ony the fields/columns you really need

And learn about indices ... I guess this is the actual problem behind the current question.

Siegfried

[yuri_ziryukin]

... Please be aware that with performance there is no 'do always' and 'do never', it always depends.

That's what I was trying to tell

I certainly agree with the general approach to use sorted tables when possible.

However in this particular case the topic starter asked to optimize his code from the performance point of view. And that's what I did introducing as little changes as possible.

[Former Member]

Siegfried Boes wrote:
 ... Please be aware that with performance there is no 'do always' and 'do never', it always depends.
That's what I was trying to tell 

Then we all agree... please note I never use the word always or never... Just gave what I think is the best practice: use sorted table (you seem to agree) and avoid using DELETE inside a loop (this is generally adviced, I don't really understand why you are so upset about this...)

When googling it you will see that usually this is consider as a "good" standard...

Here is the first I found (you can get many more links on the subject, here or outside) <link to blocked site removed by moderator>

or many thread like:

Now I must thank you for taking the time to test it and showing us that performance is not really impacted... However, I will always prefer avoiding nested operation when possible....

Br,

Manu.

Edited by: Manu D'Haeyer on Nov 10, 2011 1:53 PM

Edited by: Thomas Zloch on Nov 10, 2011 2:06 PM

[yuri_ziryukin]

> Then we all agree... please note I never use the word always or never... Just gave what I think is the best practice: use sorted table (you seem to agree) and avoid using DELETE inside a loop (this is generally adviced, I don't really understand why you are so upset about this...)

It was because of the wording. When I read words "performance standards", I take it serious and try to understand why should it be a performance standard. And the delete inside the loop case has actually nothing to do with performance. That's why I decided to comment it.

As simple as that.

[Former Member]

And the delete inside the loop case has actually nothing to do with performance

Sorry... still not really agree... but that's your point. I'd like to have some SAP mentor comment on this

Just try the worst case scenario (lets delete all those 400000 entries and not just couple of them), I think, as the number of entries to delete grows, you will see the performance issue... Moreover, I've never heard anybody advicing to delete a record from a table while processing it (at least for the sole reason described by suhas)...

Anyway... I found all your other advices great! so please don't take this too personaly

Kr,

Manu.

[yuri_ziryukin]

> 
> Anyway... I found all your other advices great! so please don't take this too personaly 
> 
> Kr,
> Manu.

Sure, no problem.

Here is the test if many entries need to be deleted. Something like 399.000 out of 400.000.

Delete in loop:

Perform SORT_DELETE 1 513.580 = 513.580

Delete outside of loop:

Perform SORT_DELETE 1 367.587 = 367.587

In that case indeed, the delete outside of the loop is faster.

So, you are right. Kernel seems to do something extra if the entry is deleted inside the loop.

I'll keep it in mind.

[Former Member]

Yuri - I believe part of the increased time for doing this in the LOOP will be the added overhead of calling the FORM multiple times.

Rob

[yuri_ziryukin]

Yuri - I believe part of the increased time for doing this in the LOOP will be the added overhead of calling the FORM multiple times.
> 
> Rob

Hello Rob,

no, the form is called exactly once in both cases.

Here is the code:

form sort_delete.

  sort lt_item by guid.

  loop at lt_order assigning <fs_order>.
    read table lt_item with key guid = <fs_order>-guid binary search
      transporting no fields.
    if sy-subrc <> 0.
      <fs_order>-del = 'X'.
    endif.
  endloop.
  delete lt_order where del = 'X'.

endform.

and

form sort_delete.

  sort lt_item by guid.

  loop at lt_order assigning <fs_order>.
    read table lt_item with key guid = <fs_order>-guid binary search
      transporting no fields.
    if sy-subrc <> 0.
      delete lt_order.
    endif.
  endloop.

endform.                    "sort_delete

[HermannGahm]

Hi Yuri,

>Here is the test if many entries need to be deleted. Something like 399.000 out of 400.000.

what's the result if you delete only a few out of 400.000? it should be vice versa....

Kind regards,

Hermann

[yuri_ziryukin]

Hi Yuri,
> 
> >Here is the test if many entries need to be deleted. Something like 399.000 out of 400.000.
> 
> what's the result if you delete only a few out of 400.000? it should be vice versa....
> 
> Kind regards,
> 
> Hermann

Hello Hermann,

no, it's not vice versa. Then the performance is the same (statistical difference of few milliseconds).

Yuri

[HermannGahm]

Hi Yuri,

ok, THAT surprises me a little bit. For small volumes to be deleted i would have expected the delete in the loop to be faster.

For big volumes to be deleted i can understand that the delete where is faster.

what happens if you avoid the delete where for big volumes but append the records that should suvive in a table

and overwrite the orignal big table with the small table (containing the surviving records)?

Kind regards,

Hermann

[yuri_ziryukin]

Hi Yuri,
> what happens if you avoid the delete where for big volumes but append the records that should suvive in a table
> and overwrite the orignal big table with the small table (containing the surviving records)?

I'll test it tomorrow, have to leave earlier today

[Former Member]

Hi,

I found some good stuff on the subject in SAP press:

http://www.sap-press.de/download/dateien/1880/sappress_abap_performance_tuning.pdf

Where you can find this interesting quote:

Set-based accesses to internal tables, for instance, LOOP ... WHERE or DELETE ... WHERE, are emulated by the ABAP VM and can be mapped in an optimized way for some table types

There is also some information about the sort...

Sorting is a runtime-intensive statement regardless of whether the sorting is implemented in the main memory or in the file system.
Therefore, only sort if this is absolutely required by the application.

That's all folks

Kr,

Manu.