Performance

[Former Member]

Hi Experts

This is my coding part, Its performance is very bad, Can you tell me what changes needs to be done for improving the performance of the report.

Regards

Rajaram

FORM retrieve_data .

• Retrieve Payer Details

SELECT SINGLE * FROM payr WHERE zbukr = p_bukrs

AND vblnr = p_vblnr

AND gjahr = p_gjahr

AND voidr = '00'.

• IF sy-subrc IS NOT INITIAL.

• SELECT SINGLE * FROM payr WHERE zbukr = p_bukrs

• AND vblnr = p_vblnr

• AND gjahr = p_gjahr.

• ENDIF.

• For Posting Key 29.

PERFORM item_details_29_25.

• GET data based on Clearing Documents

• IF sy-subrc NE 0. gkb

SELECT bukrs

belnr

gjahr

shkzg

skfbt

dmbtr

wskto

qbshb

lifnr

bschl

nebtr

FROM bseg

INTO TABLE ibseg

WHERE augbl = p_vblnr

AND bukrs = p_bukrs

AND koart = 'K'

• AND gjahr = p_gjahr " gkb

AND auggj = p_gjahr

AND belnr NE p_vblnr.

LOOP AT ibseg.

• Net Amount

• If Amount is Credited

IF ibseg-shkzg EQ 'H'.

IF ibseg-skfbt IS NOT INITIAL.

item-gamt = ibseg-skfbt. "Gross Amt

ELSE.

item-gamt = ibseg-dmbtr. "Gross Amt gkb

ENDIF.

item-otd = ibseg-wskto. "Other Deductions

• tds

SELECT SINGLE dmbtr FROM bseg INTO item-tds

WHERE belnr EQ ibseg-belnr

AND gjahr EQ ibseg-gjahr

AND bukrs EQ ibseg-bukrs

AND ktosl EQ 'WIT'.

IF sy-subrc NE 0.

item-tds = ibseg-qbshb. "TDS

ENDIF.

• ITEM-NAMT = IBSEG-DMBTR. "Net Amount

• if tds is available tds = gross amount - other deductions

IF item-otd IS INITIAL.

item-namt = ibseg-dmbtr. "Net Amount

ELSE.

item-namt = item-gamt - item-otd .

ENDIF. "if ITEM-OTD is initial.

• added by

IF item-namt is initial.

select single dmbtr from bsak into lv_dmbtr

where bukrs = ibseg-bukrs

and lifnr = ibseg-lifnr

and belnr = ibseg-belnr

and gjahr = ibseg-gjahr

and blart LIKE 'C%'.

lv_dmbtr = lv_dmbtr * -1.

MOVE lv_dmbtr to item-namt.

ENDIF.

• Retrieve Reference Document no and Posting date

SELECT SINGLE * FROM bkpf WHERE belnr = ibseg-belnr

AND gjahr = ibseg-gjahr.

IF sy-subrc IS INITIAL.

item-xblnr = bkpf-xblnr. " Refernce Number

item-budat = bkpf-budat. "Posting Date

ENDIF.

• If amount is debited

ELSEIF ibseg-shkzg EQ 'S'.

• IF Posting equal to 25

IF ibseg-bschl EQ '25' .

item-gamt = ibseg-dmbtr. "Gross Amt

• tds

SELECT SINGLE dmbtr FROM bseg INTO item-tds

WHERE belnr EQ ibseg-belnr

AND gjahr EQ ibseg-gjahr

AND bukrs EQ ibseg-bukrs

AND ktosl EQ 'WIT'.

IF sy-subrc NE 0.

item-tds = ibseg-qbshb. "TDS

ENDIF.

• ITEM-TDS = IBSEG-QBSHB. "TDS

item-otd = ibseg-wskto. "Other Deductions

• ITEM-NAMT = IBSEG-NEBTR * -1. " Net Amount

• if tds Value is vailable

IF item-otd IS INITIAL.

item-namt = ibseg-nebtr * -1. "Net Amount

ELSE.

item-namt = item-gamt - item-otd * -1.

ENDIF. "if ITEM-OTD is initial.

IF item-namt is initial.

select single dmbtr from bsak into lv_dmbtr

where bukrs = ibseg-bukrs

and lifnr = ibseg-lifnr

and belnr = ibseg-belnr

and gjahr = ibseg-gjahr

and blart LIKE 'C%'.

lv_dmbtr = lv_dmbtr * -1.

MOVE lv_dmbtr to item-namt.

ENDIF.

• Retrieve Header Details

SELECT SINGLE * FROM bkpf WHERE belnr = ibseg-belnr

AND gjahr = ibseg-gjahr.

IF sy-subrc IS INITIAL.

item-xblnr = bkpf-xblnr. " Refernce Number

item-budat = bkpf-budat. "Posting Date

ENDIF.

elseif ibseg-bschl eq '27' .

item-gamt = ibseg-dmbtr. "Gross Amt

• tds

select single dmbtr from bseg into item-tds

where belnr eq ibseg-belnr

and gjahr eq ibseg-gjahr

and bukrs eq ibseg-bukrs

and ktosl eq 'WIT'.

if sy-subrc ne 0.

item-tds = ibseg-qbshb. "TDS

endif.

• ITEM-TDS = IBSEG-QBSHB. "TDS

item-otd = ibseg-wskto. "Other Deductions

• ITEM-NAMT = IBSEG-NEBTR * -1. " Net Amount

• if tds Value is vailable

if item-otd is initial.

item-namt = ibseg-nebtr * -1. "Net Amount

else.

item-namt = item-gamt - item-otd * -1.

endif. "if ITEM-OTD is initial.

if item-namt is initial.

select single dmbtr from bsak into lv_dmbtr

where bukrs = ibseg-bukrs

and lifnr = ibseg-lifnr

and belnr = ibseg-belnr

and gjahr = ibseg-gjahr

and blart like 'C%'.

lv_dmbtr = lv_dmbtr * -1.

move lv_dmbtr to item-namt.

endif.

• Retrieve Header Details

select single * from bkpf where belnr = ibseg-belnr

and gjahr = ibseg-gjahr.

if sy-subrc is initial.

item-xblnr = bkpf-xblnr. " Refernce Number

item-budat = bkpf-budat. "Posting Date

endif.

ELSEIF ibseg-bschl EQ '29' .

item-gamt = ibseg-nebtr. "Gross Amt

• tds

SELECT SINGLE dmbtr FROM bseg INTO item-tds

WHERE belnr EQ ibseg-belnr

AND gjahr EQ ibseg-gjahr

AND bukrs EQ ibseg-bukrs

AND ktosl EQ 'WIT'.

IF sy-subrc NE 0.

item-tds = ibseg-qbshb. "TDS

ENDIF.

• ITEM-TDS = IBSEG-QBSHB. "TDS

item-otd = ibseg-wskto. "Other Deductions

• ITEM-NAMT = IBSEG-NEBTR * -1. " Net Amount

• if tds Value is vailable

IF item-otd IS INITIAL.

item-namt = ibseg-dmbtr * -1. "Net Amount

ELSE.

item-namt = item-gamt - item-otd * -1.

ENDIF. "if ITEM-OTD is initial.

• Retrieve Header Details

SELECT SINGLE * FROM bkpf WHERE belnr = ibseg-belnr

AND gjahr = ibseg-gjahr.

IF sy-subrc IS INITIAL.

item-xblnr = bkpf-xblnr. " Refernce Number

item-budat = bkpf-budat. "Posting Date

ENDIF.

****gkb

ELSEIF ibseg-bschl EQ '21' .

item-gamt = ibseg-dmbtr. "Gross Amt

• tds

SELECT SINGLE dmbtr FROM bseg INTO item-tds

WHERE belnr EQ ibseg-belnr

AND gjahr EQ ibseg-gjahr

AND bukrs EQ ibseg-bukrs

AND ktosl EQ 'WIT'.

IF sy-subrc NE 0.

item-tds = ibseg-qbshb. "TDS

ENDIF.

• ITEM-TDS = IBSEG-QBSHB. "TDS

item-otd = ibseg-wskto. "Other Deductions

• ITEM-NAMT = IBSEG-NEBTR * -1. " Net Amount

• if tds Value is vailable

IF item-otd IS INITIAL.

item-namt = ibseg-dmbtr * -1. "Net Amount

ELSE.

item-namt = item-gamt - item-otd * -1.

ENDIF. "if ITEM-OTD is initial.

• Retrieve Header Details

SELECT SINGLE * FROM bkpf WHERE belnr = ibseg-belnr

AND gjahr = ibseg-gjahr.

IF sy-subrc IS INITIAL.

item-xblnr = bkpf-xblnr. " Refernce Number

item-budat = bkpf-budat. "Posting Date

ENDIF.

****gkb

ELSEIF ibseg-bschl EQ '26'.

CLEAR bseg.

SELECT SINGLE * FROM bseg

WHERE augbl = ibseg-belnr

AND bukrs = p_bukrs

AND gjahr = ibseg-gjahr

AND belnr NE ibseg-belnr.

SELECT shkzg

dmbtr

qbshb

wskto

nebtr

FROM bseg

INTO CORRESPONDING FIELDS OF TABLE ibsegz

WHERE augbl = ibseg-belnr

AND bukrs = p_bukrs

AND auggj = ibseg-gjahr

AND belnr NE ibseg-belnr.

LOOP AT ibsegz.

ibsegy-dmbtr = ibsegz-dmbtr .

ibsegy-qbshb = ibsegz-qbshb.

ibsegy-wskto = ibsegz-wskto.

ibsegy-nebtr = ibsegz-nebtr.

COLLECT ibsegy.

• COLLECT ibsegz into ibsegy.

CLEAR : ibsegz.

ENDLOOP.

LOOP AT ibsegy.

item-gamt = ibsegy-dmbtr. "Gross Amt

item-tds = ibsegy-qbshb. "TDS

item-otd = ibsegy-wskto. "Other Deductions

item-namt = ibsegy-nebtr * -1. "Net Amount

IF ibsegy-dmbtr EQ ibsegy-nebtr.

ibsegy-nebtr = ibsegy-dmbtr - ibsegy-qbshb.

ENDIF.

• if tds is not initial.

IF item-otd IS INITIAL.

item-namt = ibsegy-dmbtr * -1. "Net Amount

ELSE.

item-namt = item-gamt - item-otd * -1.

ENDIF. "if ITEM-OTD is initial.

CLEAR: ibsegy.

ENDLOOP.

• Retrieve Header Details

SELECT SINGLE * FROM bkpf WHERE belnr = bseg-belnr

AND gjahr = bseg-gjahr.

IF sy-subrc IS INITIAL.

item-xblnr = bkpf-xblnr. " Refernce Number

item-budat = bkpf-budat. "Posting Date

ENDIF.

REFRESH : ibsegy,

ibsegz.

****gkb

ELSE.

CLEAR bseg.

SELECT SINGLE * FROM bseg

WHERE augbl = ibseg-belnr

AND bukrs = p_bukrs

AND gjahr = ibseg-gjahr

AND belnr NE ibseg-belnr.

SELECT shkzg

dmbtr

qbshb

wskto

nebtr

FROM bseg

INTO CORRESPONDING FIELDS OF TABLE ibsegz

WHERE augbl = ibseg-belnr

AND bukrs = p_bukrs

AND auggj = ibseg-gjahr

AND belnr NE ibseg-belnr.

LOOP AT ibsegz.

IF ibsegz-shkzg = 'H'.

ibsegy-dmbtr = ibsegz-dmbtr * -1 .

ELSE.

ibsegy-dmbtr = ibsegz-dmbtr .

ENDIF.

ibsegy-qbshb = ibsegz-qbshb.

ibsegy-wskto = ibsegz-wskto.

ibsegy-nebtr = ibsegz-nebtr.

COLLECT ibsegy.

CLEAR : ibsegz.

ENDLOOP.

LOOP AT ibsegy.

item-gamt = ibsegy-dmbtr. "Gross Amt

item-tds = ibsegy-qbshb. "TDS

item-otd = ibsegy-wskto. "Other Deductions

• item-namt = ibsegy-nebtr * -1. "Net Amount

IF ibsegy-dmbtr EQ ibsegy-nebtr.

ibsegy-nebtr = ibsegy-dmbtr - ibsegy-qbshb.

ENDIF.

• if tds is not initial.

IF item-otd IS INITIAL.

• item-namt = ibsegy-nebtr * -1. "Net Amount

item-namt = ibsegy-dmbtr * -1. "Net Amount

ELSE.

item-namt = item-gamt - item-otd * -1.

ENDIF. "if ITEM-OTD is initial.

CLEAR: ibsegy.

ENDLOOP.

• Retrieve Header Details

SELECT SINGLE * FROM bkpf WHERE belnr = bseg-belnr

AND gjahr = bseg-gjahr.

IF sy-subrc IS INITIAL.

item-xblnr = bkpf-xblnr. " Refernce Number

item-budat = bkpf-budat. "Posting Date

ENDIF.

REFRESH : ibsegy,

ibsegz.

ENDIF. "IF IBSEG-BSCHL EQ '25'

ENDIF.

APPEND item.

CLEAR item.

ENDLOOP.

*****

IF count = 0.

SELECT shkzg

skfbt

dmbtr

wskto

belnr

gjahr

bukrs

qbshb

lifnr

bschl

nebtr

FROM bseg

INTO TABLE ibsegx

WHERE bukrs = p_bukrs

AND belnr = p_vblnr

AND gjahr = p_gjahr

AND bschl = '50'

AND qsskz NE ' '.

ENDIF.

****

IF NOT ibsegx[] IS INITIAL.

LOOP AT ibsegx.

item-tds = ibsegx-dmbtr. "TDS

item-namt = ibsegx-dmbtr * -1.

• Retrieve Header Details

SELECT SINGLE * FROM bkpf WHERE belnr = ibsegx-belnr

AND gjahr = ibsegx-gjahr.

IF sy-subrc IS INITIAL.

IF NOT bkpf-xblnr IS INITIAL.

item-xblnr = bkpf-xblnr. " Refernce Number

ELSE.

item-xblnr = 'TDS on Payment'.

ENDIF.

item-budat = bkpf-budat. "Posting Date

ENDIF.

APPEND item.

CLEAR item.

ENDLOOP.

ENDIF.

****

• ENDIF. gkb

• IF item[] IS INITIAL.

• RETRIEVE ITEM DETAILS.

SELECT * FROM bseg

WHERE belnr = p_vblnr

AND gjahr = p_gjahr

AND koart = 'K'

AND bukrs = p_bukrs

• AND bschl = '25'.

AND bschl IN ('25','38')

AND augbl NE p_vblnr.

PERFORM get_refdoc.

item-xblnr = bkpf-xblnr. " Refernce Number

item-budat = bkpf-budat. "Posting Date

item-gamt = bseg-dmbtr. "Gross Amt

item-tds = bseg-qbshb. "TDS

item-otd = bseg-wskto. "Other Deductions

• ITEM-NAMT = BSEG-NEBTR. " Net Amount

• if tds is not initial

IF item-otd IS INITIAL.

item-namt = bseg-nebtr. "Net Amount

ELSE.

item-namt = item-gamt - item-otd.

ENDIF. "if ITEM-OTD is initial.

COLLECT item.

CLEAR item.

ENDSELECT.

• ENDIF.

• IF TDS IS DEDUCTED DURING PAYMENT DOCUMENT CREATION -

ENDFORM. " Retrieve_data

[Former Member]

Hi,

Please remove the SQLs like bseg, bkpf from inside the loop and move it outside

Thanks

Naren

[Former Member]

Instead of removing it from loop, can i use BSIK and BSAK tables inside the loop.

because i need to do these operations within the loop.

And what are the other statements which mainly affect the performance badly.

And give me the suggestions to improve it.

Regards

Rajaram

[Former Member]

Hi

If your starting point for the selection is Clearing document number, then first select records from BSAK. From BSAK go to BSEG and use all the keys. BSEG is a cluster table , never try to fetch records from here without all the keys.

Avoid repeated select in BSEG.

if you do this , the performance will improve 90 percent.

Dont forget to reward with points .

[Former Member]

Hi Rajaram,

First, you should clean up your code and modularize your code so it is easier to understand and maintain. Then you may see problem areas easier.

For example, your main ibseg loop should look like:

LOOP AT ibseg.

• If Amount is Credited

IF ibseg-shkzg EQ 'H'.

PERFORM DO_CREDIT.

• If amount is debited

ELSEIF ibseg-shkzg EQ 'S'.

PERFORM DO_DEBIT.

ENDIF.

APPEND item.

CLEAR item.

ENDLOOP.

Your forms shouldn't be more than a couple of pages long.

Also, I went through your code and you can reduce the amount of code by 70%. There is a lot of redundant code in your program.

Your IF ibseg-bschl EQ '25' and

ELSEIF ibseg-bschl EQ '27' are identical and can be reduced to one routine.

Your "TDS" routine occurs 5 times in your code. Code it once in a form and call the form.

SELECT SINGLE dmbtr FROM bseg INTO item-tds

WHERE belnr EQ ibseg-belnr

AND gjahr EQ ibseg-gjahr

AND bukrs EQ ibseg-bukrs

AND ktosl EQ 'WIT'.

IF sy-subrc NE 0.

item-tds = ibseg-qbshb. "TDS

ENDIF.

Your Get Header Details occurs 8 times, 5 times are identical, 2 are the same and 1 is slightly different.

If you call it from a routine and pass the belnr and gjahr, you can create one form with parameters and call it 8 times.

SELECT SINGLE * FROM bkpf

WHERE belnr = ibseg-belnr

AND gjahr = ibseg-gjahr.

IF sy-subrc IS INITIAL.

item-xblnr = bkpf-xblnr. " Refernce Number

item-budat = bkpf-budat. "Posting Date

ENDIF.

I'm not sure how your program works with sy-subrc is initial. Initial is not the same as 0. You should change it regardless

as initial normally means empty not 0.

These suggestions don't necessarily make the code faster, just better coding practice.

Some performance suggestions though:

- use INTO TABLE rather than CORRESPONDING FIELDS OF. This means you have to change the fields of your internal table to

match the order of your fields in the SELECT statement.

- using LIKE 'C%' is not recommended. Instead you should put all the doc types in a range table or code it like:

AND BLART in ('C1', 'C2', 'C3') and so on, how ever many you have.

- as suggested use other tables rather than BSEG when possible.

- use of Select * is not preferred, specifying fields is better, but Select single * is not too bad

- look at your Selects and try to make sure the tables you use have indexes on the fields in your where clause. You

may need to create new indexes but this is a last resort and requires careful examination of your code.

Well, I hope this helps.

Filler

[Former Member]

Please run this through ST05. This will help you find any SQL problems.

Rob