Performance Issue with BSEG

[Former Member]

These are the queries i am using in my report to fetch the line item data from bseg.functional guys are very adamant and dont want to use any other tables than BSEG.Kindly suggest me what can be done to improve the performance as this report takes a lot of time to run.

Suggest me how can i optimize the queries involved for improved performance.

SELECT BELNR BUKRS FROM BSEG INTO TABLE L_DOC_NO

WHERE BUKRS IN S_BUKRS

AND GJAHR = P_GJAHR

AND HKONT IN S_SAKNR.

IF SY-SUBRC <> 0.

MESSAGE ID 'ZTFI' TYPE 'E' NUMBER 006.

ENDIF.

CLEAR L_DOC_NO.

SORT L_DOC_NO BY BELNR.

• Selecting the Document Number Based on the selection-screen.

SELECT BELNR BUKRS BUDAT CPUDT BLART MONAT FROM BKPF INTO TABLE L_BKPF

FOR ALL ENTRIES IN L_DOC_NO

WHERE BUKRS = L_DOC_NO-BUKRS AND

BELNR = L_DOC_NO-BELNR AND

GJAHR = P_GJAHR AND

BUDAT IN S_BUDAT AND

MONAT IN S_MONAT.

IF SY-SUBRC <> 0.

MESSAGE ID 'ZTFI' TYPE 'E' NUMBER 006.

ENDIF.

*Fetch the Line Items

SORT L_BKPF BY BELNR.

SELECT BELNR BUKRS BUZEI HKONT SHKZG WRBTR FROM BSEG INTO TABLE L_BSEG

FOR ALL ENTRIES IN L_BKPF

WHERE BUKRS = L_BKPF-BUKRS

AND BELNR = L_BKPF-BELNR

AND GJAHR = P_GJAHR

AND BUZEI BETWEEN '001' AND '999'.

IF SY-SUBRC <> 0.

MESSAGE ID 'ZTFI' TYPE 'E' NUMBER 006.

ENDIF.

[Former Member]

These are the queries i am using in my report to fetch the line item data from bseg.functional guys are very adamant and dont want to use any other tables than BSEG.Kindly suggest me what can be done to improve the performance as this report takes a lot of time to run.

Suggest me how can i optimize the queries involved for improved performance.

SELECT BELNR BUKRS FROM BSEG INTO TABLE L_DOC_NO

WHERE BUKRS IN S_BUKRS

AND GJAHR = P_GJAHR

AND HKONT IN S_SAKNR.

IF SY-SUBRC <> 0.

MESSAGE ID 'ZTFI' TYPE 'E' NUMBER 006.

ENDIF.

CLEAR L_DOC_NO.

SORT L_DOC_NO BY BELNR.

• Selecting the Document Number Based on the selection-screen.

SELECT BELNR BUKRS BUDAT CPUDT BLART MONAT FROM BKPF INTO TABLE L_BKPF

FOR ALL ENTRIES IN L_DOC_NO

WHERE BUKRS = L_DOC_NO-BUKRS AND

BELNR = L_DOC_NO-BELNR AND

GJAHR = P_GJAHR AND

BUDAT IN S_BUDAT AND

MONAT IN S_MONAT.

IF SY-SUBRC <> 0.

MESSAGE ID 'ZTFI' TYPE 'E' NUMBER 006.

ENDIF.

*Fetch the Line Items

SORT L_BKPF BY BELNR.

SELECT BELNR BUKRS BUZEI HKONT SHKZG WRBTR FROM BSEG INTO TABLE L_BSEG

FOR ALL ENTRIES IN L_BKPF

WHERE BUKRS = L_BKPF-BUKRS

AND BELNR = L_BKPF-BELNR

AND GJAHR = P_GJAHR

AND BUZEI BETWEEN '001' AND '999'.

IF SY-SUBRC <> 0.

MESSAGE ID 'ZTFI' TYPE 'E' NUMBER 006.

ENDIF.

[Former Member]

since BSEG is a cluster table.. any querry on that table will hamper the perfirmance...

use more conditions in where clause to reduce the search area..

otherwise ask for other tables from where to fetch the data....

[Former Member]

Hi...

Try this....

DATA: s_cursor TYPE cursor.

OPEN CURSOR WITH HOLD s_cursor FOR

SELECT BELNR BUKRS FROM BSEG

WHERE BUKRS IN S_BUKRS

AND GJAHR = P_GJAHR

AND HKONT IN S_SAKNR.

DO.

FETCH NEXT CURSOR s_cursor

APPENDING

TABLE L_DOC_NO

PACKAGE SIZE '2000'.

IF sy-subrc <> 0.

CLOSE CURSOR s_cursor.

EXIT.

ENDIF.

ENDDO

IF L_DOC_NO[] is initial.

MESSAGE ID 'ZTFI' TYPE 'E' NUMBER 006.

ENDIF.

CLEAR L_DOC_NO.

SORT L_DOC_NO BY BELNR.

• Selecting the Document Number Based on the selection-screen.

OPEN CURSOR WITH HOLD s_cursor FOR

SELECT BELNR BUKRS BUDAT CPUDT BLART MONAT FROM BKPF

FOR ALL ENTRIES IN L_DOC_NO

WHERE BUKRS = L_DOC_NO-BUKRS AND

BELNR = L_DOC_NO-BELNR AND

GJAHR = P_GJAHR AND

BUDAT IN S_BUDAT AND

MONAT IN S_MONAT.

DO.

FETCH NEXT CURSOR s_cursor

APPENDING

TABLE L_BKPF

PACKAGE SIZE '2000'.

IF sy-subrc <> 0.

CLOSE CURSOR s_cursor.

EXIT.

ENDIF.

ENDDO

IF L_BKPF[] is initial.

MESSAGE ID 'ZTFI' TYPE 'E' NUMBER 006.

ENDIF.

*Fetch the Line Items

SORT L_BKPF BY BELNR.

OPEN CURSOR WITH HOLD s_cursor FOR

SELECT BELNR BUKRS BUZEI HKONT SHKZG WRBTR FROM BSEG

FOR ALL ENTRIES IN L_BKPF

WHERE BUKRS = L_BKPF-BUKRS

AND BELNR = L_BKPF-BELNR

AND GJAHR = P_GJAHR

AND BUZEI BETWEEN '001' AND '999'.

DO.

FETCH NEXT CURSOR s_cursor

APPENDING

TABLE L_BSEG

PACKAGE SIZE '2000'.

IF sy-subrc <> 0.

CLOSE CURSOR s_cursor.

EXIT.

ENDIF.

ENDDO

IF L_BSEG[] is initial.

MESSAGE ID 'ZTFI' TYPE 'E' NUMBER 006.

ENDIF.

[Former Member]

Hi Mohammad,

Why are you selecting records from table BSEG twice? Table BKPF and BSEG are usually very large tables. The more the selects the worse the performance. Look at the code below. It does the same thing you are trying to do and will work faster.

TYPES: BEGIN OF ty_bkpf,
         bukrs TYPE bkpf-bukrs,
         belnr TYPE bkpf-belnr,
         budat TYPE bkpf-budat,
         cpudt TYPE bkpf-cpudt,
         blart TYPE bkpf-blart,
         monat TYPE bkpf-monat,
       END OF ty_bkpf,

       BEGIN OF ty_bseg,
         belnr TYPE bseg-belnr,
         bukrs TYPE bseg-bukrs,
         buzei TYPE bseg-buzei,
         hkont TYPE bseg-hkont,
         shkzg TYPE bseg-shkzg,
         wrbtr TYPE bseg-wrbtr,
       END OF ty_bseg.

DATA: t_bkpf TYPE TABLE OF ty_bkpf,
      t_bseg TYPE TABLE OF ty_bseg.

SELECT bukrs
       belnr
       budat
       cpudt
       blart
       monat
  FROM bkpf
  INTO TABLE t_bkpf
  WHERE bukrs IN s_bukrs
  AND   gjahr EQ p_gjahr
  AND   budat IN s_budat
  AND   monat IN s_monat.

IF sy-subrc EQ 0.

  SELECT belnr
         bukrs
         buzei
         hkont
         shkzg
         wrbtr
    FROM bseg
    INTO TABLE t_bseg
    FOR ALL ENTRIES IN t_bkpf
    WHERE bukrs EQ t_bkpf-bukrs
    AND   belnr EQ t_bkpf-belnr
    AND   gjahr EQ t_bkpf-gjahr
    AND   hkont IN s_saknr.

ENDIF.

[ThomasZloch]

Your main selection is by BUKRS and HKONT. This demands using the index tables BSIS and BSAS to quickly find line items for a selection of G/L accounts, provided "line item display" is switched on for all G/L accounts.

So your access would be BSIS and BSAS then select "for all entries" on BKPF. And BKPF is only needed for CPUDT, since all other fields you are selecting are available from BSIS and BSAS.

Cheers

Thomas