perfomance problem when deleting 20lakh records from internal table

[Former Member]

LOOP AT itab .

DELETE itab1 WHERE belnr = itab-belnr.

ENDLOOP

there are 20 lakh records in itab as well as itab1. The execution of this loop is taking a lot of time. Can anyone please suggest an efficient and fastest way to acheive the above requirement. Also i have to delete around 19 lakh entires from itab1 and i cannot filter the data using select queires.

I have tried the below logic too but even that is taking a lot time.

loop at itab1.

read table itab with key belnr = itab1-belnr

binary search transporting no fields.

if sy-subrc <> 0.

append itab1 to itab2.

endif.

endloop.

kindly suggest an efficient logic.

Thanks,

Gopi Krishna

[Former Member]

Hi,

Try yo give the condition at 'loop at' and check.

LOOP AT itab WHERE belnr = itab-belnr.

DELETE itab1.

ENDLOOP.

Regards,

[vinod_vemuru2]

Hi Gopi,

DELETE in LOOP is worse. How ever in some places like this we can't avoid it.

Did u sorted both the tables by BELNR.

With out Sort DELETE will work even worse and BINARY

SEARCH will not help us

Check below efficient way for ur requirement.

SORT: itab BY belnr,

itab1 BY belnr.

LOOP AT itab1.

READ TABLE itab WITH KEY belnr = itab-belnr

TRANSPORTING NO FIELDS

BINARY SEARCH.

CHECK NOT sy-subrc IS INITIAL.

"If record is not there in itab then append it.

APPEND itab1 TO itab2.

ENDLOOP.

Header lines will be Much slower than Explicit Work areas. So use explicit work areas instead of header lines.

U can check the performance of Headerline and work area in Transaction SE30.

I feel Appending to new table will be much faster than DELETE

If it is still taking time then i suggest u to schedule the object as batch job or Get the records in to itab and itab1 in chunks of 100000 and process them.

Thanks,

Vinod.

[Former Member]

i have sorted the both tables by belnr and used the header lines. Still the performance is worse. It is taking more than 4 days to come execute the loop when i scheduled it in background. Any more suggestions are highly appreciated.

[Former Member]

Hi

Don't know if you are stilling checking this - have you tried making itab (the one you are reading/deleting) a Hashed table? There will be an overhead in populating it but it will make a huge difference when you come to reading/deleting it. There will be other implciations but you can look all that up in the online help for table types.

Hope that helps

Andrew

Edited by: Andrew Wright on Jun 5, 2008 11:44 AM

[Former Member]

Hi,

You can do as below:

Sort itab by bukrs belnr.

sort itab1 by bukrs belnr.

loop at itab.

Read table itab1 with key bukrs = itab-bukrs
                                      belnr = itab-belnr.

   if sy-subrc <> 0.
     delete itab1.
     clear itab1.
     continue.
  endif.

endloop.

Thanks,

Sriram Ponna.