Skip to Content
author's profile photo Former Member
Former Member

Performance Issue with BSEG

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.

Add a comment
10|10000 characters needed characters exceeded

Related questions

4 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Apr 11, 2008 at 07:11 AM

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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Apr 11, 2008 at 01:32 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Apr 11, 2008 at 02:16 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Apr 11, 2008 at 02:27 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.