Performance issue-Report

Former Member · ‎2007 Jun 12

Hi,

I have developed FI report which is having 18 laks records, the time taken is more than 45 hrs,please any one can help me in the performance wise.

SELECT ZBUKR

RZAWE

LIFNR

KUNNR

ZNME1

VBLNR

RWBTR

WAERS

ZALDT

VOIDD

VOIDR

BANCD

CHECT FROM PAYR INTO TABLE IT_PAYR

WHERE RZAWE IN S_RZAWE

AND ZALDT IN S_ZALDT

AND BANCD IN S_BANCD

AND VOIDD IN S_VOIDD

AND LIFNR IN S_LIFNR

AND KUNNR IN S_KUNNR.

IF SY-SUBRC = 0.

SORT IT_PAYR BY VBLNR.

DELETE ADJACENT DUPLICATES FROM IT_PAYR

COMPARING VBLNR GJAHR.

else.

ENDIF.

SORT IT_PAYR BY VBLNR LIFNR KUNNR.

*******Fetches data from PAYR Table******

IF NOT IT_PAYR[] IS INITIAL.

IT_VEND[] = IT_PAYR[].

SORT IT_VEND BY LIFNR.

DELETE IT_VEND WHERE LIFNR EQ SPACE.

DELETE ADJACENT DUPLICATES FROM IT_VEND COMPARING LIFNR.

SELECT LIFNR

BRSCH

INTO TABLE IT_LFA1

FROM LFA1

FOR ALL ENTRIES IN IT_PAYR

WHERE LIFNR EQ IT_PAYR-LIFNR.

IT_CUST[] = IT_PAYR[].

SORT IT_CUST BY KUNNR.

DELETE IT_CUST WHERE KUNNR EQ SPACE.

DELETE ADJACENT DUPLICATES FROM IT_CUST COMPARING KUNNR.

SELECT KUNNR

BRSCH

INTO TABLE IT_KNA1

FROM KNA1

FOR ALL ENTRIES IN IT_PAYR

WHERE KUNNR EQ IT_PAYR-KUNNR.

SELECT VBLNR

BELNR

BUKRS

GJAHR

KUNNR

LIFNR

ZBUKR

SHKZG FROM REGUP

INTO table it_regup

FOR ALL ENTRIES IN IT_PAYR

WHERE VBLNR EQ IT_PAYR-VBLNR

AND VBLNR NE SPACE

AND XVORL EQ SPACE.

ENDIF.

*********If internal table Regup has data then fetch BSEG data where

********belnr is equal to REGUP Belnr number**********************

IF NOT IT_REGUP[] IS INITIAL.

SELECT BUKRS

BELNR

HKONT

DMBTR

SHKZG

GJAHR

WRBTR

BUZEI

EBELN

EBELP FROM BSEG

INTO CORRESPONDING FIELDS OF TABLE IT_BSEG

FOR ALL ENTRIES IN IT_REGUP

WHERE BUKRS EQ IT_REGUP-BUKRS

AND BELNR EQ IT_REGUP-BELNR

AND GJAHR EQ IT_REGUP-GJAHR

AND SHKZG EQ 'S'.

ENDIF.

DATA: V_BVORG LIKE BKPF-BVORG.

DATA: V_M_BELNR LIKE BSEG-BELNR.

SORT IT_BSEG BY BELNR.

****Looping to Bseg table***********

LOOP AT IT_BSEG.

*Check for the GL Account Number is 120990 and credit/debit Indicator

*SHKZG is S.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'

EXPORTING

INPUT = IT_BSEG-HKONT

IMPORTING

OUTPUT = IT_BSEG-HKONT.

SHIFT IT_BSEG-HKONT LEFT DELETING LEADING '0'.

IF IT_BSEG-HKONT = '120990' AND IT_BSEG-SHKZG = 'S'. "120990

****Fetch BVORG data from BKPF table******

SELECT BELNR

BVORG FROM BKPF INTO TABLE IT_BKPF

WHERE BUKRS EQ IT_BSEG-BUKRS

AND BELNR EQ IT_BSEG-BELNR

AND GJAHR EQ IT_BSEG-GJAHR.

LOOP AT IT_BKPF INTO WA_BKPF.

MOVE WA_BKPF-BVORG TO V_BVORG.

********Fetch HKONT DMBTR BUKRS from BSEG Table**********************

SELECT BUKRS

BELNR

HKONT

DMBTR

SHKZG

GJAHR

EBELN

EBELP FROM BSEG

INTO CORRESPONDING FIELDS OF TABLE IT_BSEG1

WHERE BUKRS = V_BVORG+10(4)

AND BELNR = V_BVORG+0(10)

AND GJAHR = IT_BSEG-GJAHR.

IF IT_BSEG1-BELNR IS NOT INITIAL.

SELECT EBELN

EBELP

BELNR FROM BSEG INTO TABLE IT_PO_BSEG

FOR ALL ENTRIES IN IT_BSEG1

WHERE BELNR = IT_BSEG1-BELNR

AND SHKZG = 'S'.

ENDIF.

ENDLOOP.

ELSE.

SELECT EBELN

EBELP

BELNR FROM BSEG INTO TABLE IT_PO_BSEG

WHERE BELNR = IT_BSEG-BELNR

AND SHKZG = 'S'.

AND EBELN NE SPACE.

*ENDLOOP.

ENDIF.

*****************Checking for Capital and Expenses*********

SHIFT IT_BSEG-HKONT LEFT DELETING LEADING '0'.

IF IT_BSEG-HKONT+0(2) = '15' OR

IT_BSEG-HKONT+0(2) = '17' OR

IT_BSEG-HKONT+0(2) = '85'.

MOVE C_CAPITAL TO IT_BSEG-ACTYP.

ELSE.

IF NOT IT_BSEG-HKONT IS INITIAL.

MOVE C_EXPENSE TO IT_BSEG-ACTYP.

ENDIF.

************Conversion exit ALPHA, external->internal******

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'

EXPORTING

INPUT = IT_BSEG-HKONT

IMPORTING

OUTPUT = IT_BSEG-HKONT.

************Include Tax Accounts*************

IF IT_BSEG-HKONT IN S_TAXACC.

MOVE C_TAX TO IT_BSEG-ACTYP.

ENDIF.

MODIFY IT_BSEG TRANSPORTING ACTYP.

ENDLOOP. " END LOOP FOR BSEG.

SORT IT_PO_BSEG BY EBELN.

*********Fetch SAKTO DMBTR BUKRS from MSEG Table********************

IF NOT IT_PO_BSEG[] IS INITIAL.

DELETE IT_PO_BSEG WHERE EBELN IS INITIAL.

SELECT EBELN

EBELP

SAKTO

DMBTR

BUKRS

FROM MSEG

INTO TABLE IT_MSEG

FOR ALL ENTRIES IN IT_PO_BSEG

WHERE EBELN = IT_PO_BSEG-EBELN

AND EBELP = IT_PO_BSEG-EBELP.

IF SY-SUBRC = 0.
DELETE IT_MSEG WHERE EBELN IS INITIAL.
ENDIF.

ENDIF.

ENDFORM. " GET_DATA_PAYR

&----

*& Form final_fetch

&----

text

----

--> p1 text
<-- p2 text

----

FORM FINAL_FETCH.

LOOP AT IT_PAYR.

MOVE IT_PAYR-ZBUKR TO IT_FINAL-ZBUKR.
MOVE IT_PAYR-RZAWE TO IT_FINAL-RZAWE.
MOVE IT_PAYR-ZNME1 TO IT_FINAL-ZNME1.
MOVE IT_PAYR-VBLNR TO IT_FINAL-VBLNR.
MOVE IT_PAYR-WAERS TO IT_FINAL-WAERS.
MOVE IT_PAYR-ZALDT TO IT_FINAL-ZALDT.
MOVE IT_PAYR-VOIDD TO IT_FINAL-VOIDD.
MOVE IT_PAYR-VOIDR TO IT_FINAL-VOIDR.
MOVE IT_PAYR-BANCD TO IT_FINAL-BANCD.
MOVE IT_PAYR-CHECT TO IT_FINAL-CHECT.
MOVE IT_PAYR-RWBTR TO IT_FINAL-RWBTR.

MOVE-corresponding IT_PAYR TO IT_FINAL.

READ TABLE IT_LFA1 WITH KEY LIFNR = IT_PAYR-LIFNR

BINARY SEARCH.

IF SY-SUBRC = 0.

MOVE IT_LFA1-LIFNR TO IT_FINAL-LIFNR.

IF NOT IT_LFA1-BRSCH IS INITIAL.

MOVE IT_LFA1-BRSCH TO V_L_BRSCH.

OVERLAY V_L_BRSCH WITH '0000'.

IF V_L_BRSCH CO '1234567890'.

MOVE 'CUS ' TO V_L_BRSCH.

ELSE.

MOVE IT_LFA1-BRSCH TO V_L_BRSCH.

ENDIF.

MOVE V_L_BRSCH TO IT_FINAL-BRSCH.

ENDIF.

READ TABLE IT_KNA1 WITH KEY KUNNR = IT_PAYR-KUNNR

BINARY SEARCH.

IF SY-SUBRC = 0.

MOVE IT_KNA1-KUNNR TO IT_FINAL-KUNNR.

IF NOT IT_KNA1-BRSCH IS INITIAL.

MOVE IT_KNA1-BRSCH TO V_K_BRSCH.

OVERLAY V_K_BRSCH WITH '0000'.

IF V_K_BRSCH CO '1234567890'.

MOVE 'CUS' TO V_K_BRSCH.

ELSE.

MOVE IT_KNA1-BRSCH TO V_K_BRSCH.

ENDIF.

MOVE V_K_BRSCH TO IT_FINAL-BRSCH.

ENDIF.

READ TABLE IT_REGUP WITH KEY VBLNR = IT_PAYR-VBLNR.

LOOP AT IT_REGUP WHERE VBLNR = IT_PAYR-VBLNR.

IF SY-SUBRC = 0.

SORT IT_BSEG BY BELNR HKONT ACTYP.

CLEAR :V_SUM_TAX , V_END_EXP_DMBTR ,V_FINAL_RWBTR.

LOOP AT IT_BSEG WHERE BUKRS = IT_REGUP-BUKRS

AND BELNR = IT_REGUP-BELNR

AND GJAHR = IT_REGUP-GJAHR

AND SHKZG = 'S'.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'

EXPORTING

INPUT = IT_BSEG-HKONT

IMPORTING

OUTPUT = IT_BSEG-HKONT.

SHIFT IT_BSEG-HKONT LEFT DELETING LEADING '0'.

IF IT_BSEG-HKONT = '120990' AND IT_BSEG-SHKZG = 'S'.

READ TABLE IT_BKPF WITH KEY BELNR = IT_BSEG-BELNR

BINARY SEARCH.

IF SY-SUBRC = 0.

READ TABLE IT_BSEG1 WITH KEY BELNR = V_BVORG+0(10)

BINARY SEARCH.

IF SY-SUBRC = 0.

READ TABLE IT_PO_BSEG WITH KEY BELNR = IT_BSEG1-BELNR.

IF SY-SUBRC = 0.

READ TABLE IT_MSEG WITH KEY EBELN = IT_PO_BSEG-EBELN.

IF SY-SUBRC = 0.

MOVE IT_MSEG-SAKTO TO IT_FINAL-HKONT.

MOVE IT_MSEG-DMBTR TO IT_FINAL-WRBTR.

MOVE IT_MSEG-BUKRS TO IT_FINAL-BUKRS.

ELSE.

PERFORM GL_OFFSETTING.

ENDIF.

ELSE.

PERFORM GL_OFFSETTING.

ENDIF.

PERFORM GL_OFFSETTING.

ELSE.

PERFORM GL_OFFSETTING.

ENDIF.

ELSE.

PERFORM GL_OFFSETTING.

ENDIF.

MOVE IT_FINAL-WRBTR TO V_FINAL_RWBTR.

**********Check internal table for Taxes***********

AT END OF ACTYP.

IF IT_BSEG-ACTYP = 'EXPENSE'.

V_END_EXP_DMBTR = V_FINAL_RWBTR + V_SUM_TAX.

IF V_END_EXP_DMBTR IS NOT INITIAL.

IT_FINAL-WRBTR = V_END_EXP_DMBTR.

ENDIF.

IF IT_BSEG-ACTYP = 'CAPITAL'.

V_END_CAP_DMBTR = V_FINAL_RWBTR + V_SUM_TAX.

IF V_END_EXP_DMBTR IS NOT INITIAL.

IT_FINAL-WRBTR = V_END_EXP_DMBTR.

ENDIF.

IF IT_BSEG-ACTYP = 'TAX'.

SUM.

V_SUM_TAX = IT_FINAL-WRBTR.

ENDIF.

ENDAT.

******************End for Tax Logic******************

IF IT_BSEG-ACTYP = 'TAX'.

MOVE C_EXPENSE TO IT_FINAL-ACTYP.

ELSE.

MOVE IT_BSEG-ACTYP TO IT_FINAL-ACTYP.

ENDIF.

IT_FINAL-BELNR = IT_BSEG-BELNR.

APPEND IT_FINAL.

CLEAR IT_FINAL.

ENDLOOP.

CLEAR V_BVORG.

CLEAR IT_FINAL.

ENDLOOP.

ENDFORM. " final_fetch

&----

*& Form gl_offsetting

&----

text

----

--> p1 text
<-- p2 text

----

FORM GL_OFFSETTING .

MOVE IT_BSEG-HKONT TO IT_FINAL-HKONT.

MOVE IT_BSEG-WRBTR TO IT_FINAL-WRBTR.

MOVE IT_BSEG-BUKRS TO IT_FINAL-BUKRS.

ENDFORM. " gl_offsetting

Thanks,

Anitha

Former Member · ‎2007 Jun 12

As the number of records are high, performance may have to be compromised but as i observed, you have repeatedly written SELECT statement for BSEG and MSEG tables and those are huge tables.

Try to develop a logic so that you have to hit those tables only once.

Former Member · ‎2007 Jun 12

Hi Darshil,

The logic given by Functional consultnat is in that way. so i have to use that two tables repeatedly.

Thanks,

Anitha

seshatalpasai_madala · ‎2007 Jun 12

Hi,

You have lot of SORT statements in the program, since the number of records are high why dont you do that SORTING in the SELECT statement itself it will save some time. USe ASCENDING in your SELECT queries and sort them instead of SORT.

Regards,

Sesh

Former Member · ‎2007 Jun 12

I agree that sometimes the functional designs are written in a non-performant way, but than you still need to think how you can change this to a performant logic.

I don't agree with moving the sort from the abap to the database level, as I always heard that it's better to do this in ABAP code.

However, the first sort is not complete:

SORT IT_PAYR BY VBLNR.

DELETE ADJACENT DUPLICATES FROM IT_PAYR

COMPARING VBLNR GJAHR.

You should add GJAHR to the sort statement.

Former Member · ‎2007 Jun 12

Also the following code is not performant:

LOOP AT IT_BKPF INTO WA_BKPF.

MOVE WA_BKPF-BVORG TO V_BVORG.

********Fetch HKONT DMBTR BUKRS from BSEG Table**********************

SELECT BUKRS

BELNR

HKONT

DMBTR

SHKZG

GJAHR

EBELN

EBELP FROM BSEG

INTO CORRESPONDING FIELDS OF TABLE IT_BSEG1

WHERE BUKRS = V_BVORG+10(4)

AND BELNR = V_BVORG+0(10)

AND GJAHR = IT_BSEG-GJAHR.

IF IT_BSEG1-BELNR IS NOT INITIAL.

SELECT EBELN

EBELP

BELNR FROM BSEG INTO TABLE IT_PO_BSEG

FOR ALL ENTRIES IN IT_BSEG1

WHERE BELNR = IT_BSEG1-BELNR

AND SHKZG = 'S'.

ENDIF.

ENDLOOP.

For every record in it_bkpf you do a select on BSEG. As BSEG is very huge, this will decrease performance dramatically. It's better to use a for all entries of BKPF in your select on BSEG.

Also try to avoid " into corresponding fields of", as it generates abap overhead. Always store the exact number of fields to the internal table, in the correct order.

Former Member · ‎2007 Jun 12

Your problems appear to be from some of your SELECTS on BSEG. In at least two cases you select using only the document number. You have both company code and fiscal year in the internal table you are using for the selects, so add them:


    SELECT ebeln ebelp belnr
      FROM bseg 
      INTO TABLE it_po_bseg

      WHERE bukrs = it_bseg-bukrs
        and belnr = it_bseg-belnr
        and gjahr = it_bseg-gjahr
        AND shkzg = 'S'.

Rob

Former Member · ‎2007 Jun 12

Another problem is in the form final_fetch. I stripped out some code and it looks like:


FORM final_fetch.

  LOOP AT it_payr.

    LOOP AT it_regup WHERE vblnr = it_payr-vblnr.

      LOOP AT it_bseg WHERE bukrs = it_regup-bukrs
                        AND belnr = it_regup-belnr
                        AND gjahr = it_regup-gjahr
                        AND shkzg = 'S'.

      ENDLOOP.
    ENDLOOP.
  ENDLOOP.

ENDFORM. " final_fetch

This is a triply nested loop. You didn't show how you are calling it, but if you are calling it in a loop or there are more than a few thousand records in the internal tables when it is called, performance can be degraded more than the unindexed SELECT statements. You can use the techniques in this blog to deal with the nested loops:

/people/rob.burbank/blog/2006/02/07/performance-of-nested-loops

If you take care of the two problems I've shown, your program will run much more quickly.

Rob

ThomasZloch · ‎2007 Jun 12

Why don't you run Code Inspector over your code and work through the errors and warnings in the performance section?

It will find those incomplete key accesses that Rob has pointed out and probably much more.

Also, I still have to see a functional spec that can be translated 1:1 into a clean, performant piece of code without further analysis by the techies (that's us). So butt heads with your functional consultant until there is a clean solution to the problem.

I'm proud of my zero points so please don't award any.

Cheers

Thomas

TusharP · ‎2007 Jun 12

Try using standard function modules to extract data rather than using select on huge tables such as BSEG and MSEG

Thanks

Tushar