Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Performance issue

Former Member

‎03-16-2009 12:26 PM

0 Kudos

hello,

Can anybody tell me how can i increse performance in following queries it takes lot of time..

SELECT mblnr                         " Number of Material Document
         mjahr                         " Material Document Year
         budat                         " Posting Date in the Document
    FROM mkpf
    INTO TABLE i_mkpf
   WHERE budat
      IN s_budat
     AND vgart = 'WE'.

  IF sy-subrc NE 0.
    MESSAGE 'No incoming Invoices exist'(002) TYPE 'I'.
    STOP.
  ELSE.
    SORT i_mkpf BY mblnr mjahr .
    DELETE ADJACENT DUPLICATES FROM i_mkpf
                               COMPARING mblnr mjahr.
  ENDIF.                               " IF sy-subrc NE 0

  IF i_mkpf IS NOT INITIAL.

    SELECT ebeln                       " Purchase Order Number
           ebelp                       " Item Number of Purchasing Document
           mblnr                       " Number of Material Document
           mjahr                       " Material Document Year
           zeile                       " Item In Material Document
           lfbnr                       " Document No. of a Reference Document
           lfbja                       " Fiscal Year of a Reference Document
           lfpos                       " Item of a Reference Document
           werks                       " Plant
           matnr                       " Material Number
           lifnr                       " Vendor Account Number
      FROM mseg
INTO TABLE i_mseg
       FOR ALL ENTRIES IN i_mkpf
     WHERE mblnr = i_mkpf-mblnr
       AND mjahr = i_mkpf-mjahr
       AND bwart = '101'
       AND werks IN s_werks.

    IF sy-subrc = 0.
      SORT i_mseg BY ebeln ebelp mblnr mjahr zeile.
      DELETE ADJACENT DUPLICATES FROM i_mseg
                            COMPARING ebeln ebelp mblnr
                                      mjahr zeile.
    ENDIF.                             " IF sy-subrc = 0
  ENDIF.                               " If i_mkpf is not initial


  IF i_mseg IS NOT INITIAL.
    SELECT ebeln                       " Purchasing Document Number
           ebelp                       " Item Number of Purchasing Doc
           belnr                       " Miro Document Number
           gjahr                       " Miro Document Year
           buzei                       " Item in Miro Document
           budat                       " Miro Posting Date
           dmbtr                       " Miro Amount In Local Currency
           lfgja                       " Fiscal Year of a Reference Doc.
           lfbnr                       " Document No. of a Reference Doc.
           lfpos                       " Item of a Reference Document
           vgabe                       " Transaction/event type, purchase order history
      FROM ekbe
INTO TABLE i_ekbe_miro1
       FOR ALL ENTRIES IN i_mseg
     WHERE ebeln =  i_mseg-ebeln
       AND ebelp =  i_mseg-ebelp
       AND bwart = '641'.

    IF sy-subrc EQ 0.

      SORT i_ekbe_miro1 BY ebeln.
      DELETE ADJACENT DUPLICATES FROM i_ekbe_miro1
                            COMPARING ebeln.

      LOOP AT i_ekbe_miro1 INTO fs_ekbe_miro1.
        DELETE i_mseg WHERE ebeln = fs_ekbe_miro1-ebeln.
      ENDLOOP.                         " LOOP AT i_ekbe_miro1

    ENDIF.                             " IF sy-subrc = 0

  ENDIF.                               " If i_mseg is not initial

  IF i_mseg IS NOT INITIAL.
    SELECT ebeln                       " Purchasing Document Number
           ebelp                       " Item Number of Purchasing Doc
           belnr                       " Miro Document Number
           gjahr                       " Miro Document Year
           buzei                       " Item in Miro Document
           budat                       " Miro Posting Date
           dmbtr                       " Miro Amount In Local Currency
           lfgja                       " Fiscal Year of a Reference Doc.
           lfbnr                       " Document No. of a Reference Doc.
           lfpos                       " Item of a Reference Document
           vgabe                       " Transaction/event type, purchase order history
      FROM ekbe
INTO TABLE i_ekbe_miro
       FOR ALL ENTRIES IN i_mseg
     WHERE ebeln =  i_mseg-ebeln
       AND ebelp =  i_mseg-ebelp
       AND lfgja =  i_mseg-lfbja
       AND lfbnr =  i_mseg-lfbnr
       AND lfpos =  i_mseg-lfpos
       AND werks IN s_werks
       AND vgabe = '2'.
  ENDIF.                               " if i_mseg is not initial

  IF sy-subrc EQ 0.
    SORT i_ekbe_miro BY belnr gjahr buzei.
  ENDIF.                               " IF sy-subrc EQ 0

  LOOP AT i_ekbe_miro INTO fs_ekbe_miro.

    fs_miro_ac-belnr = fs_ekbe_miro-belnr.
    fs_miro_ac-gjahr = fs_ekbe_miro-gjahr.
    fs_miro_ac-buzei = fs_ekbe_miro-buzei.
    fs_miro_ac-budat = fs_ekbe_miro-budat.
    CONCATENATE fs_ekbe_miro-belnr fs_ekbe_miro-gjahr INTO fs_miro_ac-awkey.
    APPEND fs_miro_ac TO i_miro_ac.

  ENDLOOP.                             " LOOP AT i_ekbe_miro

  SORT i_miro_ac BY belnr gjahr buzei budat awkey.
*  DELETE ADJACENT DUPLICATES FROM i_miro_ac COMPARING awkey.
  IF i_miro_ac IS NOT INITIAL.

    SELECT belnr                       " Accounting Document Number
           gjahr                       " Fiscal Year
           budat                       " Posting Date in the Document
           awkey                       " Reference Key
      FROM bkpf
INTO TABLE i_bkpf
       FOR ALL ENTRIES IN i_miro_ac
     WHERE bukrs EQ '1000'
       AND gjahr = i_miro_ac-gjahr
       AND budat = i_miro_ac-budat
       AND awkey = i_miro_ac-awkey.
  ENDIF.                               " IF i_miro_ac IS NOT INITIAL

  IF sy-subrc NE 0.
    MESSAGE 'No incoming Invoices exist'(002) TYPE 'I'.
    STOP.
  ENDIF.                               " IF sy-subrc NE 0
  IF i_mseg IS NOT INITIAL.
    SELECT mblnr                       " Number of Material Document
           mjahr                       " Material Document Year
           zeile                       " Item in Material Document
           rdoc1                       " Accounting Document
           exbed                       " Basic Excise Duty
           exaddtax1                   " Additional Tax1 value
           ecs                         " ECS Value
      FROM j_1igrxref
INTO TABLE i_excise
       FOR ALL ENTRIES IN i_mseg
     WHERE mblnr =   i_mseg-mblnr
       AND mjahr =   i_mseg-mjahr
       AND zeile =   i_mseg-zeile.
  ENDIF.                               " if i_mseg is not INITIAL
  IF sy-subrc NE 0.
    MESSAGE 'No incoming Invoices exist'(002) TYPE 'I'.
    STOP.
  ENDIF.                               " IF sy-subrc NE 0

regards

soniya.

7 REPLIES 7

Sandeep_Kumar
Advisor
Advisor

‎03-16-2009 12:43 PM

0 Kudos

There are few things which you can do :

==> search for secondary indexes on these tables .

==> use CURSOR ..open,fetch and close woth package size.

Former Member

‎03-16-2009 12:48 PM

0 Kudos

try to avoid hitting hit MKPF and MSEG tables.

instaed of them HIT Purchase order history table:EKBE.

try to use standard indexes in MKPF and MSEG.

former_member585060
Active Contributor

‎03-16-2009 1:03 PM

0 Kudos

Hi,

Don't use DELETE statement in LOOP and ENDLOOP, instead declare a field flag of type c and set that field, when the condition is met. and the you can easily delete all the records which are with flag = space with single statement.

In your case, it deletes every time record is matched and it have to regenerate the Index, so if the internal table records are more, it will add to the performance as it have to generate that many time the Delete is used.


" add this to i_mseg table defination
' flag TYPE c.

LOOP AT i_mseg INTO fs_mseg.
w_tabix = sy-tabix.
 READ TABLE i_ekbe_miro1 INTO fs_ekbe_miro1 WITH KEY ebeln = fs_mseg-ebeln.
IF sy-subrc = 0.
    fs_mseg-flag = 'X'
      MODIFY i_mseg INDEX w_tabix FROM fs_mseg TRANSPORTING flag.
  ENDIF.
  CLEAR w_tabix.
ENDLOOP.
  
DELETE FROM i_mseg WHERE flag = ' '.

Regards

Bala Krishna

Edited by: Bala Krishna on Mar 16, 2009 6:37 PM

Former Member

‎03-18-2009 7:26 AM

0 Kudos

hi,

actually i checked with SE30 for this program and according to that maximum time is taken by database only.its almost taking 90% time.Can anybody suggest me how should i reduce that time?

regards

soniya.

former_member585060
Active Contributor
In response to Former Member

‎03-18-2009 7:39 AM

0 Kudos

Hi,

Click on Hit List or F5 in SE30 Evaluate screen, before that you click on filter and check Data base interface Open and Native SQL, ok.

When you click on F5 it will show all the data Fetch with select statements, look for which fetch is taking more time, so that you can loik at that Select statement.

Regards

Bala Krishna

Former Member
In response to Former Member

‎03-18-2009 8:58 AM

0 Kudos

hi,

Maximum time is taken by MSEG table and MKPF .How can i decrese it?

regards

soniya

dev_parbutteea
Active Contributor

‎03-18-2009 9:17 AM

0 Kudos

Hi,

One advice about your codes would be do make a copy of your internal tables before doing delete adjacent duplicates else you will lose data:

SORT i_mseg BY ebeln ebelp mblnr mjahr zeile.
      DELETE ADJACENT DUPLICATES FROM i_mseg
                            COMPARING ebeln ebelp mblnr
                                      mjahr zeile.

better do this and use the tmp futher:

i_mseg_tmp[ ] = i_mseg[ ].

Regards