Performance issue

[Former Member]

Please use a meaningful subject in future

Dear sir,

SELECT BUKRS BELNR GJAHR BLART BUDAT FROM BKPF INTO TABLE IT_ADHL1 WHERE

BUKRS = 'TAFE' AND

• BLART IN BLART AND

BSTAT IN BSTAT AND

BUDAT IN BUDAT.

LOOP AT IT_ADHL1 WHERE BLART IN BLART.

MOVE-CORRESPONDING IT_ADHL1 TO IT_ADHL.

APPEND IT_ADHL. CLEAR IT_ADHL.

ENDLOOP.

SELECT BUKRS BELNR GJAHR BSCHL WRBTR HKONT GSBER MATNR FROM BSEG APPENDING TABLE IT_BSEG1

FOR ALL ENTRIES IN IT_ADHL WHERE BUKRS = IT_ADHL-BUKRS AND

BELNR = IT_ADHL-BELNR AND

GJAHR = IT_ADHL-GJAHR AND

BSCHL IN ('40','50') AND

HKONT IN HKONT.

when i execute this code in the period of 3 months ...i got a dumb (time limit exceeded) in this particular code.....how to reduce the performance........help me please.....

Thank u,

Manjula devi.D

Edited by: Matt on Mar 28, 2009 6:55 PM

[Former Member]

dont give loop directly take the table it_adhl1.

now in select compare this table dont use append.

[Former Member]

Hi,

Try using:

SORT IT_ADHL BY BUKRS BELNR GJAHR.

before the second select statement. This will improve performance.

Also checking IF IT_ADHL[] IS NOT INITIAL and DELETE ADJACENT DUPLICATES FROM IT_ADHL is a good practice.

Also MOVE_CORRESPONDING reduces performance.

Regards.

[Former Member]

Hi Manjula,

I faced some similar problem, and after I tried with this solution and posted this one.

do necessary changes as per requirement.

Author: Dileep Kumar
Submitted: 11.02.2009
Description  : Join Statement is the best in some cases as shown below than For All Entries. So Dont Forget about Joins Simply. Keep trying with joins
sometimes, where you can get a better performance.
Dont forget about joins simply like that due to performances. Now in this case(A Business Requirement) Joins is the best than FOR ALL ENTRIES.
TYPES: BEGIN OF TY_MKPF ,
        MBLNR TYPE MKPF-MBLNR,          "Material document
        MJAHR TYPE MKPF-MJAHR,
        BUDAT TYPE MKPF-BUDAT,          "Posting Date in the document
      END OF TY_MKPF.
TYPES: BEGIN OF TY_MSEG1 ,
        MJAHR TYPE MKPF-MJAHR,
        BUDAT TYPE MKPF-BUDAT,          "Posting Date in the document
        MBLNR TYPE MSEG-MBLNR,          "Material document
        MATNR TYPE MSEG-MATNR,          "Material Number
        ZEILE TYPE MSEG-ZEILE,          "Item in material document
        BWART TYPE MSEG-BWART,          "Movement Type
        CHARG TYPE MSEG-CHARG,          "Batch Number
        EBELN TYPE MSEG-EBELN,          "PO #
        SHKZG TYPE MSEG-SHKZG,          "Debit/Credit Indicator
        MENGE TYPE MSEG-MENGE,          "Quantity
        MEINS TYPE MSEG-MEINS,          "UOM
        DMBTR TYPE MSEG-DMBTR,          "Amount in local currency
        WAERS TYPE MSEG-WAERS,          "Currency Key
      END OF TY_MSEG1.
TYPES: BEGIN OF TY_MSEG ,
        MBLNR TYPE MSEG-MBLNR,          "Material document
        MATNR TYPE MSEG-MATNR,          "Material Number
        ZEILE TYPE MSEG-ZEILE,          "Item in material document
        BWART TYPE MSEG-BWART,          "Movement Type
        CHARG TYPE MSEG-CHARG,          "Batch Number
        EBELN TYPE MSEG-EBELN,          "PO #
        SHKZG TYPE MSEG-SHKZG,          "Debit/Credit Indicator
        MENGE TYPE MSEG-MENGE,          "Quantity
        MEINS TYPE MSEG-MEINS,          "UOM
        DMBTR TYPE MSEG-DMBTR,          "Amount in local currency
        WAERS TYPE MSEG-WAERS,          "Currency Key
      END OF TY_MSEG.
DATA : IT_MSEG TYPE TABLE OF TY_MSEG, WA_MSEG LIKE LINE OF IT_MSEG.
DATA : IT_MKPF TYPE TABLE OF TY_MKPF, WA_MKPF LIKE LINE OF IT_MKPF.
DATA : IT_MSEG1 TYPE TABLE OF TY_MSEG1, WA_MSEG1 LIKE LINE OF IT_MSEG1.
SELECT-OPTIONS :
   S_BUDAT FOR MKPF-BUDAT," OBLIGATORY,       " Posting Date.
   S_MATNR FOR MSEG-MATNR," OBLIGATORY.       " Material No.
   S_WERKS FOR MSEG-WERKS." OBLIGATORY,       " Plant
Start-of-Selection.
*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
    SELECT MBLNR MJAHR BUDAT
      INTO TABLE IT_MKPF
      FROM MKPF
      WHERE BUDAT IN S_BUDAT.
  IF IT_MKPF IS NOT INITIAL.
    SELECT MBLNR MATNR ZEILE BWART CHARG EBELN SHKZG MENGE MEINS DMBTR WAERS
      FROM MSEG
      INTO TABLE IT_MSEG
      FOR ALL ENTRIES IN IT_MKPF
      WHERE MBLNR = IT_MKPF-MBLNR AND
            MJAHR = IT_MKPF-MJAHR AND
            MATNR IN S_MATNR AND
            WERKS IN S_WERKS .
  ENDIF.
*compared to the above For all entries, below Join can perform well & Good.
*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
  SELECT  AMJAHR  ABUDAT BMBLNR BMATNR BZEILE BBWART B~CHARG
          BEBELN BSHKZG BMENGE BMEINS BDMBTR BWAERS
          INTO TABLE IT_MSEG1
          FROM MKPF AS A INNER JOIN MSEG AS B ON AMBLNR = BMBLNR AND
                                                 AMJAHR = BMJAHR
          WHERE A~BUDAT IN S_BUDAT AND
                B~MATNR IN S_MATNR AND
                B~WERKS IN S_WERKS .
*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 

Thanks & Regards,

Dileep .C

[Former Member]

Moved to the correct forum.

Rob

[matt]

Please use a meaningful subject in future

[Former Member]

Hi,

First of all, if it is only for doing a second fetch , you are using the internal table it_adhl, it is not required. Instead you can use the first internal table it_adhl1 itself. Becos it has all the key fields in it .

By this way you can avoid a loop execution. This will improve the program performance.

Before using the first internal table, sort and delete for adjacent duplicates. Also check whether the internal table is initial. If it is not initial, use it in the select.

Hope this will help.

Regards

Ramesh Sundaram

[Former Member]

"If you are sure that BLART (Select-Option) is not made obligatory on sel-screen but still you

"expect it should have some value before the actual SELECTion from BKPF then check if for entries.

"This way you need not DEACTIVATE/Comment "BLART" is SELECT statement and thus no need for

"any LOOP and another Int. Table IT_ADHL based on Sel-Screen/Range BLART.

IF NOT S_BLART IS INITIAL. "(Asssuming BLART as SELECT-OPTION so calling it S_BLART)

SELECT BUKRS BELNR GJAHR BLART BUDAT
  FROM BKPF
  INTO TABLE it_adhl
  WHERE BUKRS = 'TAFE' AND
               BLART IN S_BLART AND   "Uncommenting this line here from your code
               BSTAT IN BSTAT AND
               BUDAT IN BUDAT.
ENDIF.

SORT it_adhl BY bukrs belnr gjahr.
DELETE ADJACENT DUPLICATES FROM it_adhl COMPARING bukrs belnr gjahr.

IF NOT it_adhl IS INITIAL.
  SELECT BUKRS BELNR GJAHR BSCHL WRBTR HKONT GSBER MATNR 
    FROM BSEG
"    APPENDING TABLE IT_BSEG1
"Commenting above Assuming there is no previous or further need for Appending data into IT_BSEG1
    INTO TABLE it_bseg1
    FOR ALL ENTRIES IN it_adhl
    WHERE bukrs = it_adhl-bukrs AND
                 belnr = it_adhl-belnr AND
                 gjahr = it_adhl-gjahr AND
                 bschl IN ('40','50') AND
                 hkont IN s_hkont.  "Assuming s_hknot a SELECT-OPTION/RANGE
ENDIF.

"If there is still Performace Problem then check number of records fetched from BKPF table. If no. of records are too high then FOR ALL ENTRIES on BSEG would not yield performance and hence go for either of the 2 options below:

" (1) INNER JOIN on BKPF/BSEG

" (2) PACKAGE SIZE with SELECT statement for BKPF and fetch data from BSEG for those records

"in which case there could be multiple fetches from BKPF and thus from BSEG.

Edited by: Manuj Bhardwaj on May 26, 2009 10:50 PM

[Former Member]

Hi,

Try the below code and try to measure the time of your select queries in se30.

select bukrs

belnr

gjahr

blart

budat

from bkpf

into table it_bkpf

where bukrs eq gc_tafe

and bstat in s_bstat

and budat in s_budat.

if it_bkpf-blart in s_blart and if it_bkpf[] is not initial.

select bukrs

belnr

gjahr

bschl

wrbtr

hkont

gsber

matnr

from bseg

into table it_bseg

for all entries in it_bkpf

where bukrs eq it_bkpf-bukrs

and belnr eq it_bkpf-belnr

and gjahr eq ity_bkpf-gjahr

and bschl in ( gc_40 , gc_50 )

and hkont in s_hkont.

endif.

Regards,

Rizwana