performance

[Former Member]

here is a piece of code where i am facing problem:

FORM F001-FETCH_DATA .

• FETCH HEADER LEVEL DATA

SELECT BUKRS

BELNR

BLART

BLDAT

BUDAT

XBLNR

FROM BKPF

INTO TABLE IT_BKPF

WHERE BUKRS = PR_COMP

AND BLART IN (W_YA, W_YB, W_YC)

AND BUDAT IN SO_BUDAT.

• AND BLDAT IN SO_BLDAT.

IF SY-SUBRC <> 0.

MESSAGE E000(8I) WITH TEXT-043.

ENDIF.

if not it_bkpf[] is initial. "+PS101007

sort it_bkpf by belnr. "+PS101007

• FETCH ITEM LEVEL DATA

SELECT BELNR

KOART

SHKZG

GSBER

MWSKZ

WRBTR

SGTXT

HKONT

KUNNR

ZFBDT

ZTERM

MATNR

WERKS

MENGE

MEINS

XREF1

XREF2

FROM BSEG

INTO TABLE IT_BSEG

FOR ALL ENTRIES IN IT_BKPF

WHERE BELNR = IT_BKPF-BELNR

AND KUNNR IN SO_CUST

AND MWSKZ IN SO_TAX

AND GSBER IN SO_BUS.

ENDIF. "+PS101007

here as so many records are getting selected in 1st SELECT statement its getting stuck in the 2nd SELECT.

how to solve this problem?

plz help...

[Former Member]

Can you please check if any other option is viable in the 2nd select statement rather than using FOR ALL ENTRIES IN IT_BKPF.Just try using the key field from

IT_BKPF to filter the value in the 2nd select statement.

Thanks,

Rupesh

[Former Member]

hi rupesh,

thanks.

no other option is available as BSEG is cluster table we cannot use JOIN so we only have FOR ALL ENTRIES.

i have given all key fields in selection criteria.

can u suggest anything else apart from this?

[Former Member]

since your SELECT-OPTION includes KUNNR, you should be using tables BSID and BSAD instead of BKPF and BSEG. These are secondary index tables based on KUNNR.

rob

[Former Member]

Hi Prachi,

BKPF and BSEG are large tables. Select statements on them usually take long. I noticed the following about your code:

1) You are not using complete primary keys while selecting data from BSEG. You need to include field GJAHR in your field list in table IT_BKPF. (Look at code sample below).

2) If you go from BKPF to BSEG, effectively you have most of the fields of the primary key of table BSEG. However in your code you don't seem to be taking advantage of this. You need to use BUKRS, BELNR and GJAHR in your where clause in the select on BSEG.

3) When you use FOR ALL ENTRIES you need to include all the primary key fields in your field list to aviod the loss of duplicate data. You need to include field BUKRS, GJAHR and BUZEI (not sure of the last field as I do not have access to the system now. I am typing all this on my notepad at home. Check the data dictionary to verify if the last field in the primary key is correct.)

4) Finally (this does not have any bearing on the performance of your code), avoid using error messages in the START-OF-SELECTION or END-OF-SELECTION events (You have done so if no record is found in BKPF) because it will abruptly terminate your program. Preferably use a write statement to write the error message to a classical report. Irrespective of the results, your report should allow the user to go back to his selection screen if he selects the BACK arrow button.

SELECT bukrs
       belnr
       blart
       bldat
       budat
       xblnr
       gjahr " Add this field as it is part of the primary key
FROM bkpf
INTO TABLE it_bkpf
WHERE bukrs EQ pr_comp
AND   blart IN (w_ya, w_yb, w_yc)
AND   budat IN so_budat.

IF sy-subrc <> 0.

*  message e000(8i) with text-043.
   FORMAT COLOR col_negative ON.
   WRITE:/ text-043.
   FORMAT COLOR col_negative OFF.

ELSE.

* Sort the table by the entire primary key not just BELNR
  SORT it_bkpf BY bukrs belnr gjahr.

  SELECT belnr
         koart
         shkzg
         gsber
         mwskz
         wrbtr
         sgtxt
         hkont
         kunnr
         zfbdt
         zterm
         matnr
         werks
         menge
         meins
         xref1
         xref2
         bukrs "Add in order to avoid data loss
         gjahr "Add in order to avoid data loss
         buzei "Add in order to avoid data loss
    FROM bseg
    INTO TABLE it_bseg
    FOR ALL ENTRIES IN it_bkpf
    WHERE bukrs EQ it_bkpf-bukrs "You have BUKRS. Use it
    AND   belnr EQ it_bkpf-belnr
    AND   gjahr EQ it_bkpf-gjahr "We have added this in BKPF field 
    AND   kunnr IN so_cust
    AND   mwskz IN so_tax
    AND   gsber IN so_bus.

ENDIF.

[Former Member]

hi mark,

thanks.i hv done tese changes...lets hope it works...

thnx once again.

[Former Member]

hi guys,

my previous problem is solved.now its taking time in some other loop that i am giving here:

LOOP AT it_bkpf INTO wa_bkpf.

w_indx = sy-tabix.

SORT it_bseg BY belnr. "+PS101007

READ TABLE it_bseg INTO wa_bseg

WITH KEY belnr = wa_bkpf-belnr BINARY SEARCH. "+PS101007

IF sy-subrc <> 0.

DELETE it_bkpf INDEX w_indx.

ENDIF.

ENDLOOP.

is there any change required here in the loop?

plz reply ASAP.

[ThomasZloch]

move the "SORT it_bseg" outside (before) the loop. Sort by bukrs belnr and gjahr. Then in the loop read it_bseg TRANSPORTING NO FIELDS with these three key fields, since you are only checking for existence.

Cheers

Thomas

[Former Member]

I have modified your code with comments.

* I assume this is the structure of table IT_BKPF
* Add field DEL to the end of this structure as shown below
TYPES: BEGIN OF ty_bkpf,
         bukrs  TYPE bkpf-bukrs,
         belnr  TYPE bkpf-belnr,
         blart  TYPE bkpf-blart,
         bldat  TYPE bkpf-bldat,
         budat  TYPE bkpf-budat,
         xblnr  TYPE bkpf-xblnr,
         gjahr  TYPE bkpf-gjahr,
         del(1) TYPE c         , "Deletion flag
       END OF ty_bkpf.

DATA: w_indx  TYPE          sy-tabix,
      wa_bkpf TYPE          ty_bkpf ,
      it_bkpf TYPE TABLE OF ty_bkpf .

* Notice that we have sorted the tables outside the loop. We are
* sorting both the tables just once
SORT: it_bkpf BY bukrs belnr gjahr,
      it_bseg BY bukrs belnr gjahr.

LOOP AT it_bkpf INTO wa_bkpf.

  w_indx = sy-tabix.

* 2 points to be noted here.
* 1) Use of complete primary key of table BKPF to pick the exact match
* 2) Use of TRANSPORTING NO FIELDS. Notice that we have not used a
*    header work area. Since we are not transporting any data we don't
*    need it. We are only checking for the existance of the record in
*    internal table IT_BSEG
  READ TABLE it_bseg
  WITH KEY bukrs = wa_bkpf-bukrs
           belnr = wa_bkpf-belnr
           gjahr = wa_bkpf-gjahr
           BINARY SEARCH
           TRANSPORTING NO FIELDS.

* When no match is found we are only marking the record for deletion not
* deleting the record here.
  IF sy-subrc <> 0.
    wa_bkpf-del = 'X'.
    MODIFY it_bkpf FROM wa_bkpf TRANSPORTING del.
  ENDIF.

ENDLOOP.

* Notice that we are deleting all the mismatched records with just one
* delete statement. This is so much more efficient as compared to
* deleting within the loop
DELETE it_bkpf WHERE del = 'X'.

Let me know if this helps.