topic enhancing performance in Application Development and Automation Discussions

enhancing performance

Former Member — Mon, 19 Nov 2007 04:49:40 GMT

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

Re: enhancing performance

naimesh_patel — Mon, 19 Nov 2007 04:54:50 GMT

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

Re: enhancing performance

Former Member — Mon, 19 Nov 2007 05:23:45 GMT

HI,

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

thanks,

Khan

Re: enhancing performance

Former Member — Mon, 19 Nov 2007 14:20:39 GMT

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

Re: enhancing performance

Former Member — Mon, 19 Nov 2007 14:26:56 GMT

Hi Khan,

Don't use INTO CORRESPONDING FIELDS.

Use Secondary Indexes if available.

Reward if this helps,

Satish

Re: enhancing performance

Former Member — Mon, 19 Nov 2007 15:30:54 GMT

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

Re: enhancing performance

Former Member — Tue, 20 Nov 2007 02:46:54 GMT

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.

Re: enhancing performance

Former Member — Tue, 20 Nov 2007 12:00:09 GMT

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....

Re: enhancing performance

ThomasZloch — Tue, 20 Nov 2007 15:40:48 GMT

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

Re: enhancing performance

Former Member — Tue, 20 Nov 2007 15:57:32 GMT

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