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.

[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)?
> 
> Kind regards,
> 
> Hermann

Hello Hermann,

if many entries should be deleted (only few survived), the runtime of this option is better.

Comparing to the option with DELETE after loop it is 188 ms vs. 343 ms correspondingly. But both of them are better than DELETE inside the loop.

If few entries should be deleted (many survivors), the runtime of this option is a little worse.

Comparing to the option with DELETE after loop it is 265 ms vs. 234 ms.

Regards,

Yuri

P.S. new 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 ne 0.
      append <fs_order> to lt_order2.
*      <fs_order>-del = 'X'.
    endif.
  endloop.
  lt_order[] = lt_order2.

endform.

Edited by: Yuri Ziryukin on Nov 11, 2011 4:31 PM

Edited by: Yuri Ziryukin on Nov 11, 2011 6:06 PM

[volker_borowski2]

Just to get a point ...

You have 400k records in lt_item.

How many do you have in lt_order ?

If they are near to each other, the "binary search" might be the part that needs to be tackled.

(As you know I am more the database kind, and for two large sets to be joined,

a merge is usually better than a nested loop (even when the nested loop is index (binary search) supported)

Esp. as in Yuris program the original key of GJAHR (NUM 4) and BELNR (CHAR(!) 10) is exchanged

with a guid that usually can be stored in a CPU register. So the binary search in the sample program is

as simple as possible, while in the original one the binary search consists of 2^19 (= ~512000) char/num comparisons.

MIght be difficult to measure as we have only RAM acces here.

Volker

Ok, these are not 2^{19 comparisons, but 19 comparisons to tackle a mass of 2}19 ~ 512000 records in a binary search.

Sorry,

V.

Edited by: Volker Borowski on Nov 12, 2011 3:40 PM

[HermannGahm]

Hi Yuri,

thanks for your test.

i made a few times the experience:

if many records need to be delted, avoiding the delete can be an better option.

in such a case: avoiding the delete might be the best option, a mass delete (delete where) the 2nd best option

and single line deletes the slowest.

What still puzzles me:

if we just delete a few lines from a big internal table you said the few single line deletes are as fast as

a delete where? that suprises me i would have expected that the single line deletes should be faster.

May i see your whole code for this test case?

From a theoretic point of view:

for a single line delete like this one:

DELETE ITAB. "index specification not required in loop

we only have to maintain the index of the internal table if the record is not in the end of the table. if the index is not there yet it has to be created first. the cost of indexmaintenance itself depends on where the deleted entry is in the index (begin or end).

the delete where on the other hand:

DELETE itab WHERE del = 'X'

has to do a loop on the table and collect (append to an internal table) all the recordpositionsn (index) that should be deleted. this internal table is then handed over to the kernel (kernel function) as input in order to delete. so we have indexmaintenance as well.

so my assumption is it should be more expensive. especially for small volumes to be deleted (we have still the loop as overhead).

for big volumes to be deleted i would assume some internal optimizations (e.g. range deletes in the index.... )

That's why i ask for your exact source code for the case if we delete only a few lines:

When you do the first delete of these:

DELETE ITAB. "index specification not required in loop

what happend to the table before? does it has an index? or has the index to be created?

same for this one:

DELETE itab WHERE del = 'X'

when executed, what happened to the table before? does it has and index? or has the index to be created?

Kind regards,

Hermann

[yuri_ziryukin]

Hello Hermann,

here is the complete code:

types: begin of t_order,
         guid type crmt_object_guid,
         object_id type crmt_object_id_db,
       end of t_order,
       begin of t_item,
         guid type crmt_object_guid,
       end of t_item.

data: lt_order type table of t_order,
      lv_tst1 type timestampl,
      lv_tst2 type timestampl,
      lv_runtime type p decimals 3,
      lt_item type table of t_item.

field-symbols: <fs_order> like line of lt_order.

select guid object_id into table lt_order from crmd_orderadm_h.
* approx. 20.000 entries. Append more
select guid object_id appending table lt_order from crmd_orderadm_h.
select guid object_id appending table lt_order from crmd_orderadm_h.
select guid object_id appending table lt_order from crmd_orderadm_h.
select guid object_id appending table lt_order from crmd_orderadm_h.
append lines of lt_order to lt_order. "now 200.000
append lines of lt_order to lt_order. "now 400.000

select guid into table lt_item from crmd_opport_h.

get time stamp field lv_tst1.
perform sort_delete.
get time stamp field lv_tst2.
lv_runtime = lv_tst2 - lv_tst1.
write: 'runtime:', lv_runtime.
*&---------------------------------------------------------------------*
*&      Form  sort_delete
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
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 ne 0.
      delete lt_order.
    endif.
  endloop.

endform.                    "sort_delete

lt_order is a Standard Table[397120x2(36)]

lt_item is a Standard Table[57x1(16)]

By changing "if sy-subrc ne 0" to "if sy-subrc = 0" I can simulate if many or few entries have to be deleted. In the case above - many entries will be deleted.

The alternative code of the form routine for delete after loop you can see in my other post.

Regards,

Yuri

[yuri_ziryukin]

Update.

It might be that I have made an error during my testing.

After rewriting the test program as advised by Hermann, I see the following results.

Many entries to be deleted:

runtime of delete in loop 1,313 s

runtime of delete where after loop 0,937 s

Few entries to be deleted:

runtime of delete in loop 0,641 s

runtime of delete where after loop 1,047 s

So, the delete in loop is better if only few entries have to be deleted.

[HermannGahm]

Hi Yuri,

thanks for the update. Now practise matches theory

> Many entries to be deleted:
> runtime of delete in loop 1,313 s
> runtime of delete where after loop 0,937 s
> 
> Few entries to be deleted:
> runtime of delete in loop 0,641 s
> runtime of delete where after loop 1,047 s
> 
> So, the delete in loop is better if only few entries have to be deleted.

And as an addition: if many lines should be deleted, replacing the delete where by alternative logic that keeps the survivers can be better.

But the main problem with deletes is that we ususally don't know in advance how many records will be deleted...

Kind regards,

Hermann

[kesavadas_thekkillath]

Hi,

As this whole thread is about using delete statament, I would like to add some

I generally donot use delete inside the loop, instead i move the satisfied(required) entries to another itab. I have read this tip in some links,the reason it says is the delete in the internal table results in restructuring of the internal table index.

Kesav

[HermannGahm]

Hi Kesav,

Hi,
> 
> As this whole thread is about using delete statament, I would like to add some 
> 
> I generally donot use delete inside the loop, instead i move the satisfied(required) entries to another itab. I have read this tip in some links,the reason it says is the delete in the internal table results in restructuring of the internal table index.
> 
> Kesav

well, the index maintenance cost can not always be avoided and usually don't dominate the run time...

Kind regards,

Hermann

[SuhaSaha]

But the main problem with deletes is that we ususally don't know in advance how many records will be deleted...  

Hello All,

Can we not populate the data in a local SORTED TABLE with deletion-field as the key-field so that we can optimise the DELETE?

DELETE itab WHERE del_field EQ <value>

BR,

Suhas

[kesavadas_thekkillath]

Hi Suhas,

I was just checking that. Delete on the sorted table was slower than the linear one. Is the index updation the reason ?

DATA:it TYPE TABLE OF mara,
     s TYPE i,e TYPE i,d TYPE i,
     wa TYPE mara,
     it2 TYPE TABLE OF mara,
     it3 TYPE TABLE OF mara WITH NON-UNIQUE KEY meins.

START-OF-SELECTION.

  SELECT * FROM mara INTO TABLE it UP TO 50000 ROWS.
  CHECK sy-subrc = 0.

* Individual delete without Loop
  it2[] = it[].
  GET RUN TIME FIELD s.
  DELETE it WHERE meins EQ 'EA'.   "Less Execution time
  GET RUN TIME FIELD e.
  d = e - s.
  WRITE :' Individual delete without Loop( Standard Table )',d.
  SKIP 1.

* Append to new itab
  it[] = it2[].
  GET RUN TIME FIELD s.
  LOOP AT it INTO wa.
    IF wa-mtart NE 'EA'.
      APPEND wa TO it2.
    ENDIF.
  ENDLOOP.
  GET RUN TIME FIELD e.
  d = e - s.
  WRITE :'Append to new internal table( Standard Table )',d.
  SKIP 1.

* Delete inside loop without where clause
  it[] = it2[].
  GET RUN TIME FIELD s.
  LOOP AT it INTO wa.
    IF wa-meins EQ 'EA'.
      DELETE it INDEX sy-tabix.
    ENDIF.
  ENDLOOP.
  GET RUN TIME FIELD e.
  d = e - s.
  WRITE :'Delete inside loop without where clause( Standard Table )',d.
  SKIP 1.

  SORT it2 BY meins.
  APPEND LINES OF it2 TO it3.
  GET RUN TIME FIELD s.
  DELETE it3 WHERE meins EQ 'EA'.
  GET RUN TIME FIELD e.
  d = e - s.
  WRITE :'Delete with sorted table',d.

[SuhaSaha]

Delete on the sorted table was slower than the linear one.

I was talking about SORTED TABLEs & not STANDARD TABLEs with SORT on the deletion field

DELETE...WHERE always performs linear search for STANDARD TABLE.

BR,

Suhas

[kesavadas_thekkillath]

Suhas, Internal table IT3 is of sorted table type.

My Bad !! i missed it

Kesav

But Even when its declared like

it3 TYPE SORTED TABLE OF mara WITH NON-UNIQUE KEY meins, it doesnt perform well than the linear table.

Edited by: Keshav.T on Nov 14, 2011 4:24 PM

Edited by: Keshav.T on Nov 14, 2011 4:25 PM

[HermannGahm]

Hi,

Suhas, Internal table IT3 is of sorted table type.
> 
> But Even when its declared like
>  it3 TYPE SORTED TABLE OF mara WITH NON-UNIQUE KEY meins, it doesnt perform well than the linear table.
> Edited by: Keshav.T on Nov 14, 2011 4:25 PM

sure... if your where condition for the sorted table is not selective... it won't be better than a scan on a standard table....

Kind regards,

Hermann