enhancing performance

[Former Member]

hi experts,

I have z report which uses BSEG & BKPF tables. it taks time to execute. I ran code inspector but it didnt show any error in performance check tab. But still it takes around 95% Database time in Runtime AnalysisEvaluation. How could I enhance its performance. where I should search that where it takes more time. Below is the select statement that i used in z report.

******************************************************************************************

SELECT BELNR BUDAT FROM BKPF INTO CORRESPONDING FIELDS OF TABLE IBKPF WHERE BLDAT IN S_DATE AND GJAHR = F_YEAR AND BUKRS EQ P_BUKRS.

IF NOT IBKPF[] IS INITIAL.

SELECT BELNR KUNNR BUKRS GJAHR BUZEI SHKZG VALUT SGTXT ZFBDT ZUONR DMBTR PSWSL ZTERM VBELN VORGN WERKS

FROM BSEG INTO CORRESPONDING FIELDS OF TABLE ITAB

FOR ALL ENTRIES IN IBKPF

WHERE BELNR = IBKPF-BELNR AND

BUKRS EQ P_BUKRS AND

KOART EQ 'D' AND

( UMSKZ EQ SPACE OR UMSKZ EQ 'A') AND

ZUMSK EQ SPACE AND

KUNNR IN S_KUNNR AND

GJAHR = F_YEAR.

ENDIF.

LOOP AT ITAB.

..........................

...........................

ENDLOOP.

**************************************************************************************

tHANKS.

Khan

[naimesh_patel]

Your BKPF query should be

Make IBKPF with only 2 field BELNR BUDAT

ranges: r_belnr for bkpf-belnr.

SELECT BELNR BUDAT 
FROM BKPF 
INTO TABLE IBKPF 
WHERE BUKRS EQ P_BUKRS
and  belnr in r_belnr  " << range with null
AND GJAHR = F_YEAR 
AND BLDAT IN S_DATE .

Your BSEG query...

Create a ITAB with only requried field from the BSEG.

SELECT BELNR KUNNR BUKRS GJAHR BUZEI SHKZG VALUT SGTXT ZFBDT ZUONR DMBTR PSWSL ZTERM VBELN VORGN WERKS
FROM BSEG INTO TABLE ITAB
FOR ALL ENTRIES IN IBKPF
WHERE BUKRS EQ P_BUKRS 
AND BELNR = IBKPF-BELNR 
AND GJAHR = F_GJAHR
KOART EQ 'D' AND
( UMSKZ EQ SPACE OR UMSKZ EQ 'A') AND
ZUMSK EQ SPACE AND
KUNNR IN S_KUNNR .
ENDIF.

Regards,

Naimesh Patel

[Former Member]

HI,

For what Ranges used for. Can I also use Range for BUZEI to pass in BSEG.

thanks,

Khan

[Former Member]

Change your first queery thusly:

SELECT belnr budat
  FROM bkpf
  INTO CORRESPONDING FIELDS OF TABLE ibkpf
  WHERE bukrs EQ p_bukrs
    AND bstat IN (' ', 'A', 'B', 'D', 'M', 'S', 'V', 'W', 'Z')  "<====
    and BLDAT in S_DATE
    AND gjahr = f_year.

Adding BSTAT will allow the use of the index. I have used all values from the domain, but you can remove any you don't need. Also check:

<a href="/people/rob.burbank/blog/2007/11/12/quickly-retrieving-fi-document-data-from-bseg">Quickly Retrieving FI document Data from BSEG</a>

Rob

[Former Member]

I've changed both SELECTs a bit more:

SELECT bukrs belnr gjahr budat
  FROM bkpf
  INTO CORRESPONDING FIELDS OF TABLE ibkpf
  WHERE bukrs EQ p_bukrs
    AND bstat IN (' ', 'A', 'B', 'D', 'M', 'S', 'V', 'W', 'Z')  "<====
    AND bldat IN s_date
    AND gjahr = f_year.

IF NOT ibkpf[] IS INITIAL.
  SORT ibkpf BY bukrs belnr gjahr.
  DELETE adjacent duplicates FROM ibkpf comaring bukrs belnr gjahr.

  SELECT belnr kunnr bukrs gjahr buzei shkzg valut sgtxt zfbdt zuonr
         dmbtr pswsl zterm vbeln vorgn werks
    FROM bseg
    INTO CORRESPONDING FIELDS OF TABLE itab
    FOR ALL ENTRIES IN ibkpf
    WHERE bukrs = ibkpf-bukrs AND
          belnr = ibkpf-belnr AND
          gjahr = ibkpf-gjahr AND
          koart EQ 'D'        AND
        ( umskz EQ space OR umskz EQ 'A') AND
          zumsk EQ space      AND
          kunnr IN s_kunnr.
ENDIF.

Rob

[Former Member]

Hi Khan,

Don't use INTO CORRESPONDING FIELDS.

Use Secondary Indexes if available.

Reward if this helps,

Satish

[Former Member]

Try using this code.

TYPES: BEGIN OF ty_bkpf,
         belnr TYPE bkpf-belnr,
         budat TYPE bkpf-budat,
       END OF ty_bkpf,

       BEGIN OF ty_bseg,
         belnr TYPE bseg-belnr,
         buzei TYPE bseg-buzei,
         kunnr TYPE bseg-kunnr,
         shkzg TYPE bseg-shkzg,
         valut TYPE bseg-valut,
         sgtxt TYPE bseg-sgtxt,
         zfbdt TYPE bseg-zfbdt,
         zuonr TYPE bseg-dmbtr,
         dmbtr TYPE bseg-dmbtr,
         pswsl TYPE bseg-pswsl,
         zterm TYPE bseg-zterm,
         vbeln TYPE bseg-vbeln,
         vorgn TYPE bseg-vorgn,
         werks TYPE bseg-werks,
         budat TYPE bkpf-budat,
       END OF ty_bseg.

DATA: w_bkpf    TYPE                 ty_bkpf ,
      w_bseg    TYPE                 ty_bseg ,
      w_index   TYPE                 sy-tabix,
     
      ibkpf     TYPE HASHED TABLE OF ty_bkpf
        WITH UNIQUE KEY belnr,
      ibseg     TYPE        TABLE OF ty_bseg.

RANGES: r_umskz FOR bseg-umskz.

SELECT belnr 
       budat 
FROM bkpf 
INTO TABLE ibkpf 
WHERE bukrs EQ p_bukrs
AND   gjahr EQ f_year
AND   bldat IN s_date. 
 
IF sy-subrc EQ 0.

  REFRESH r_umskz.

  r_umskz-sign   = 'I' .
  r_umskz-option = 'EQ'.
  APPEND r_umskz.

  r_umskz-low    = 'A'.
  APPEND r_umskz.

  CLEAR  r_umskz.


  SELECT belnr
         buzei
         kunnr
         shkzg
         valut
         sgtxt
         zfbdt
         zuonr
         dmbtr
         pswsl
         zterm
         vbeln
         vorgn
         werks
    FROM bseg
    INTO TABLE ibseg
    FOR ALL ENTRIES IN ibkpf
    WHERE bukrs EQ p_bukrs
    AND   belnr EQ ibkpf-belnr
    AND   gjahr EQ f_year
    AND   koart EQ 'D' 
    AND   umskz IN r_umskz
    AND   zumsk eq space 
    AND   kunnr in s_kunnr. 

ENDIF.

LOOP AT ibseg INTO w_bseg.

  w_index = sy-tabix.

  AT NEW belnr.

    READ TABLE ibkpf INTO w_bkpf WITH KEY belnr = w_bseg-belnr.

    IF sy-subrc EQ 0.

      w_bseg-budat = w_bkpf-budat.

      MODIFY ibseg FROM w_bseg INDEX w_index TRANSPORTING budat.

    ENDIF.

  ENDAT.

ENDLOOP.

[Former Member]

Hi Khan,,

Please Create Index for BSEG table for which conditions have been put...

I am sure it will give good performance...

Cheers,

Sagun Desai....

[ThomasZloch]

Sagun, not possible to create index on BSEG, it is a cluster table.

Rob, imho BSTAT is only needed for BKPF-access by posting date, the indices for doc date and CPU date include BSTAT after the date field. Adding it might still help finding the correct index though.

Most of the time, selecting by BSTAT = space is sufficient anyway for the intended purpose, since some documents with BSTAT <> space might not even have line items in BSEG (e.g. clearing documents, parked documents).

Domain values for BSTAT:

<space> Normal document

A Clearing Document

B Reset clearing document

D Recurring entry document

M Sample document

S Noted items

V Parked document

W Parked document with change of document ID

Z Parked document which was deleted

[Former Member]

Thomas - you're right. I didn't see that BSTAT came after BLDAT in the index. <i>Mea culpa.</i>

I also agree that BSTAT = SPACE is sufficient in normal circumstances, but for completeness, I included all values.

Nice catch.

Rob