topic Re: Delete Statement in Application Development and Automation Discussions

Delete Statement

Former Member — Thu, 31 Aug 2006 18:10:35 GMT

I have a performance issue here, here instead of looping around XMARC to delete the records, is there any alternate to get the common records of both the internal tables into another internal table

Both these internal tables have different structures and I dont want to loop XMARC with 1,00,000 records, thats the prob...

    IF NOT S_LGORT[] IS INITIAL.
      IF NOT XMARC[] IS INITIAL.

       SELECT MATNR
              WERKS
              LGORT
         INTO TABLE IT_LGORT
         FROM MARD
         FOR ALL ENTRIES IN XMARC
       WHERE MATNR = XMARC-MATNR
         AND WERKS = XMARC-WERKS
         AND LGORT IN S_LGORT.

     SORT xmarc by matnr werks.
     SORT IT_LGORT BY MATNR WERKS.

<b>     LOOP AT XMARC.
       READ TABLE IT_LGORT WITH KEY MATNR = XMARC-MATNR
                                    WERKS = XMARC-WERKS.
          IF SY-SUBRC <> 0.
            DELETE XMARC.
          ENDIF.
     ENDLOOP.</b>    ENDIF.
  ENDIF.

Re: Delete Statement

Former Member — Thu, 31 Aug 2006 18:21:34 GMT

I think all you have to do is change your READ statement:


     LOOP AT XMARC.
       READ TABLE IT_LGORT
         WITH KEY MATNR = XMARC-MATNR
                  WERKS = XMARC-WERKS
                  <b>BINARY SEARCH</b>.

Rob

Re: Delete Statement

Former Member — Thu, 31 Aug 2006 18:25:20 GMT

Hi Rahul,

Try this:

READ TABLE IT_LGORT WITH KEY MATNR = XMARC-MATNR

WERKS = XMARC-WERKS

<b>binary search</b>.

Regards,

Vivek

Re: Delete Statement

rahulkavuri — Thu, 31 Aug 2006 18:27:20 GMT

hi my point is to delete the records without looping, can u suggest me somthing on this

Re: Delete Statement

Former Member — Thu, 31 Aug 2006 18:36:06 GMT

No - but you'll find the binary search option much faster.

Rob

Re: Delete Statement

Former Member — Thu, 31 Aug 2006 18:43:19 GMT

Check if the following is of any help

LOOP AT XMARC.

READ TABLE IT_LGORT WITH KEY MATNR = XMARC-MATNR

WERKS = XMARC-WERKS.

IF SY-SUBRC <> 0.

DELETE XMARC where matnr = XMARC-MATNR and

WERKS = XMARC-WERKS.

ENDIF.

ENDLOOP.

In your case you were only deleting one row at a time, since you want to delete all the rows of ITAB1 if its not found in ITAB2, the above would help you delete at one go and would also reduce the looping.

If however, your XMARC has unique entries for the MATNR and WERKS combination, I dont know how you will be able to delete at one go without looping.

hith

Sunil Achyut

Message was edited by: Sunil Achyut

Re: Delete Statement

Former Member — Thu, 31 Aug 2006 18:45:11 GMT

There are other problems here as well. How many records do you expect to be in IT_LGORT?

Rob

Re: Delete Statement

Former Member — Thu, 31 Aug 2006 18:45:22 GMT

Hi Rahul S Kavuri ,

i dont see a point there ..

how you can you delete entries from XMARC without having looping over it ..

Also use of binary search may prove costlier sometimes ..

Regards

Naresh

Re: Delete Statement

rahulkavuri — Thu, 31 Aug 2006 19:01:39 GMT

hi sunil there are very few duplicate records , so it increases by processing time by another 20 seconds

I expect IT_LGORT to have 1/10 th of the records of XMARC

Re: Delete Statement

Former Member — Thu, 31 Aug 2006 19:07:18 GMT

Hi Rahul,

Try this way:

loop at IT_LGORT.

delete xmarc where matnr ne it_lgort-matnr and werks ne it_lgort-werks.

endloop.

Regards,

Vivek

Re: Delete Statement

Former Member — Thu, 31 Aug 2006 19:15:09 GMT

OK - I think you are deleting the wrong record from XMZRC. Try:


REPORT ztest MESSAGE-ID 00.

<b>DATA: marc_index  LIKE sy-tabix.</b>
IF NOT s_lgort[] IS INITIAL AND
   NOT xmarc[]   IS INITIAL.

  SELECT matnr
         werks
         lgort
    INTO TABLE it_lgort
    FROM mard
    FOR ALL ENTRIES IN xmarc
  WHERE matnr = xmarc-matnr
    AND werks = xmarc-werks
    AND lgort IN s_lgort.

  SORT xmarc BY matnr werks.
  SORT it_lgort BY matnr werks.

  LOOP AT xmarc.
<b>    marc_index = sy-tabix.</b>
    READ TABLE it_lgort WITH KEY matnr = xmarc-matnr
                                 werks = xmarc-werks.
    IF sy-subrc <> 0.
      DELETE xmarc
<b>        INDEX marc_index.</b>
    ENDIF.
  ENDLOOP.
ENDIF.

If you had substantially fewer records in IT_LGORT, I would have suggested inverting the loops, but I don't think that will help here.

Rob

Re: Delete Statement

Former Member — Thu, 31 Aug 2006 19:23:26 GMT

Hi Rahul

Understanding from your code, i guess this is a report

program.

If data in XMARC is derived within your program, you

can use inner join with MARD to avoid loop and delete

concept. An example of the same will be:

  select a~matnr a~werks b~lgort into table it_mat
         from mara as a
         inner join marc as b
         on b~matnr = a~matnr
         inner join mard as c
         on c~matnr = a~matnr
         and c~werks = b~werks
         where a~matnr in s_matnr
         and   b~werks in s_werks
         and   c~lgort in s_lgort.

If my assumption is wrong and if you are deriving data from other program, then extraction from MARD

remains the same. You can ignore deleting record from

XMARC and in the later processing you can ignore entries

in XMARC whenever the material is not extended for any

storage location in the respective plant.

Ex:

   loop at XMARC.
        read table it_mard with key matnr = xmarc-matnr
                                    werks = xmarc-werks
                           binary search.
        if sy-subrc ne 0.
           continue.
        else.
*** do the processing.
        endif.
   endloop.

Note that with this method you can decrease the

execution time but you might be holding more memory for

unwanted records in XMARC.

If you need to reduce memory consumption then you have to

go with our friends advice using binary search with read statement and deleting the records in XMARC whereever the material is not extended for storage locations in

criteria. With this method i guess you will be looping

on XMARC twice, once for deleting w.r.t records in

it_mard and once for processing.

If you can post whole code of your program, i am sure

our friends here can advice better solutions basing on

your requirement.

Kind Regards

Eswar

Re: Delete Statement

Former Member — Thu, 31 Aug 2006 19:29:42 GMT

you can try this way....

loop at it_lgort.

at new werks.

DELETE TABLE xmarc WITH TABLE KEY

matnr = it_lgort-matnr

werks = it_lgort-werks.

endat.

endloop.

Re: Delete Statement

Former Member — Thu, 31 Aug 2006 19:38:30 GMT

Hi Rahul,

I am sorry for the second post....thats not correct...ignore it...

Regards,

Vivek

Re: Delete Statement

rahulkavuri — Thu, 31 Aug 2006 20:04:28 GMT

hey guys thanks for ur replies, but still these are not able to help me as they are not optimising the performance

kishore, similar suggestion is made already, infact it increases the processing time

this is the code...

<b>


    if not imatnr[] is initial.
      select * from marc into table xmarc
                      for all entries in imatnr
                      where matnr = imatnr-matnr
                      and dispo in s_dispo
                      and sobsl <> '50'.  " Phantom assembly excluded
    endif.
*-- End of changes by GUTTIS1 D51K903635
  endif.

*Start of changes                                            DEVK947965
  if not s_lgort[] is initial.
    if not xmarc[] is initial.

      select matnr
             werks
             lgort
        into table it_lgort
        from mard
        for all entries in xmarc
      where matnr = xmarc-matnr
        and werks = xmarc-werks
        and lgort in s_lgort.

*     SORT xmarc by matnr werks.
*     SORT IT_LGORT BY MATNR WERKS.
*
      sort xmarc by matnr werks.
      sort it_lgort by matnr werks.

      loop at xmarc.
        read table it_lgort with key matnr = xmarc-matnr
                                     werks = xmarc-werks
                                     transporting no fields
                                     binary search.
        if sy-subrc <> 0.
          delete xmarc.
        ENDIF.
        endloop.
      endif.
    endif.

</b>

Re: Delete Statement

Former Member — Thu, 31 Aug 2006 20:10:17 GMT

Rahul.

Just a suggestion why dont you combine the information from marc and mard in xmarc table rather than having it it_lgort.

--Surely it depends on your requirement but I think if you can combine it it would reduce the additional select..endselect.

Regards

Anurag

Re: Delete Statement

sridhar_k1 — Thu, 31 Aug 2006 21:27:07 GMT

Don't know how much performance improvement you are going to get but, Try this code for deleting records from XMARC.

Only helps if there are lot of duplicates with MATNR WERKS in XMARC

add the code after the 2 sort statements

data: l_tabix type sy-tabix.
field-symbols: <fs> type marc.

loop at xmarc.
  l_tabix = sy-tabix.
  read table it_lgort with key matnr = xmarc-matnr
                              werks = xmarc-werks
                              transporting no fields
                              binary search.
  if sy-subrc <> 0.
    assign xmarc to <fs>.
    clear sy-subrc.
    while xmarc-matnr = <fs>-matnr and xmarc-werks = <fs>-werks and sy-subrc eq 0.
      delete xmarc index l_tabix.
      add 1 to l_tabix.
      read table xmarc assigning <fs> index l_tabix.
    endwhile.
  endif.
endloop.

Regards

Sridhar

Message was edited by: Sridhar K