topic Re: Performance in Application Development and Automation Discussions

Performance

Former Member — Thu, 18 Oct 2007 11:31:27 GMT

This is done by one of my collegue, now it needs to be changed due to its bad performance.

Its performance is very poor, can anyone suggest me how and where to adjust to improve the performance of the report. Pls suggest me on this.

Regards

Rajaram

&----

*& Report ZPV

&----

REPORT zpv_new.

&----

Data Declarations

&----

TABLES : payr, bkpf,bseg.

*DATA : ibseg TYPE TABLE OF bseg WITH HEADER LINE.

*DATA : ibsegx TYPE TABLE OF bseg WITH HEADER LINE.

DATA : count TYPE c VALUE 0.

DATA: BEGIN OF ibseg OCCURS 0,

shkzg TYPE bseg-shkzg,

skfbt TYPE bseg-skfbt,

dmbtr TYPE bseg-dmbtr,

wskto TYPE bseg-wskto,

belnr TYPE bseg-belnr,

gjahr TYPE bseg-gjahr,

bukrs TYPE bseg-bukrs,

qbshb TYPE bseg-qbshb,

lifnr TYPE bseg-lifnr,

bschl TYPE bseg-bschl,

NEBTR TYPE BSEG-NEBTR,

END OF ibseg.

DATA: BEGIN OF ibsegx OCCURS 0,

shkzg TYPE bseg-shkzg,

skfbt TYPE bseg-skfbt,

dmbtr TYPE bseg-dmbtr,

wskto TYPE bseg-wskto,

belnr TYPE bseg-belnr,

gjahr TYPE bseg-gjahr,

bukrs TYPE bseg-bukrs,

qbshb TYPE bseg-qbshb,

lifnr TYPE bseg-lifnr,

bschl TYPE bseg-bschl,

NEBTR TYPE BSEG-NEBTR,

END OF ibsegx.

DATA: BEGIN OF item OCCURS 0,

xblnr TYPE bkpf-xblnr, "Reference Document No

budat TYPE bkpf-budat, "Posting Date

gamt TYPE bseg-wrbtr, "Gross Amt

tds TYPE bseg-wrbtr, "TDS

otd TYPE bseg-wrbtr, "Other Deductions

namt TYPE bseg-wrbtr, "Netamount

END OF item.

DATA: BEGIN OF ibsegy OCCURS 0,

dmbtr TYPE bseg-dmbtr,

qbshb TYPE bseg-qbshb,

wskto TYPE bseg-wskto,

nebtr TYPE bseg-nebtr,

END OF ibsegy.

DATA: BEGIN OF ibsegz OCCURS 0,

shkzg TYPE bseg-shkzg,

dmbtr TYPE bseg-dmbtr,

qbshb TYPE bseg-qbshb,

wskto TYPE bseg-wskto,

nebtr TYPE bseg-nebtr,

END OF ibsegz.

DATA: v_banks TYPE bnka-banks,

v_bankl TYPE bnka-bankl,

v_banka TYPE bnka-bankl,

v_ort01 TYPE bnka-brnch,

v_sfname TYPE rs38l_fnam,

v_shkzg TYPE bseg-shkzg,

lv_dmbtr TYPE bseg-dmbtr.

&----

Selection-screen

&----

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-000.

PARAMETERS : p_bukrs TYPE payr-zbukr OBLIGATORY DEFAULT '1000',

"Company Code

p_vblnr TYPE payr-vblnr OBLIGATORY, "Voucher No

p_gjahr TYPE payr-gjahr OBLIGATORY memory id GJR.

" DEFAULT sy-datum+0(4)

"Fiscal Year

SELECTION-SCREEN END OF BLOCK b1.

&----

Start-of-selection

&----

START-OF-SELECTION.

PERFORM retrieve_data.

PERFORM dispaly_smartform.

&----

*& Form Retrieve_data

&----

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 shkzg

skfbt

dmbtr

wskto

belnr

gjahr

bukrs

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

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.

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

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

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

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

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

&----

*& Form Dispaly_Smartform

&----

FORM dispaly_smartform .

Retrieve Function Module Name

CALL FUNCTION 'SSF_FUNCTION_MODULE_NAME'

EXPORTING

formname = 'ZPV'

VARIANT = ' '
DIRECT_CALL = ' '

IMPORTING

fm_name = v_sfname.

EXCEPTIONS
NO_FORM = 1
NO_FUNCTION_MODULE = 2
OTHERS = 3

IF sy-subrc <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

Call Function Module

CALL FUNCTION v_sfname

EXPORTING

ARCHIVE_INDEX =
ARCHIVE_INDEX_TAB =
ARCHIVE_PARAMETERS =
CONTROL_PARAMETERS =
MAIL_APPL_OBJ =
MAIL_RECIPIENT =
MAIL_SENDER =
OUTPUT_OPTIONS =
USER_SETTINGS = 'X'

payr = payr

IMPORTING
DOCUMENT_OUTPUT_INFO =
JOB_OUTPUT_INFO =
JOB_OUTPUT_OPTIONS =

TABLES

item = item[]

EXCEPTIONS
FORMATTING_ERROR = 1
INTERNAL_ERROR = 2
SEND_ERROR = 3
USER_CANCELED = 4
OTHERS = 5

IF sy-subrc <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

ENDFORM. " Dispaly_Smartform

&----

*& Form GET_REFDOC

&----

FORM get_refdoc .

Retrieve Header Details

IF bseg-rebzg IS INITIAL.

SELECT SINGLE * FROM bkpf WHERE belnr = bseg-belnr

AND gjahr = p_gjahr

AND bukrs = p_bukrs.

ELSE.

SELECT SINGLE * FROM bkpf WHERE belnr = bseg-rebzg

AND gjahr = p_gjahr

AND bukrs = p_bukrs.

ENDIF. "if bseg-bschl eq '29'.

ENDFORM. " GET_REFDOC

&----

*& Form item_details_29_25

&----

FORM item_details_29_25 .

Retrieve Item Details.

SELECT * FROM bseg

WHERE belnr = p_vblnr

AND gjahr = p_gjahr

AND koart = 'K'

AND bukrs = p_bukrs

AND bschl = '29'.

AND BSCHL IN ('29','25').

IF sy-subrc IS INITIAL. " kishore 20.06.2006

count = 1.

ENDIF.

PERFORM get_refdoc.

item-xblnr = bkpf-xblnr. " Refernce Number

item-budat = bkpf-budat. "Posting Date

item-gamt = bseg-dmbtr. "Gross Amt

item-otd = bseg-wskto. "Other Deductions

item-namt = bseg-nebtr. "Netamount

IF bseg-augbl IS INITIAL .
IF TDS IS DEDUCTED DURING PAYMENT DOCUMENT CREATION -

SELECT SINGLE dmbtr shkzg FROM bseg

INTO (item-tds ,v_shkzg)

WHERE belnr = p_vblnr

AND bukrs = p_bukrs

AND gjahr = p_gjahr

AND ktosl EQ 'WIT'.

IF sy-subrc IS INITIAL.

SELECT SINGLE * FROM bkpf WHERE belnr = bseg-belnr

AND gjahr = p_gjahr

AND bukrs = p_bukrs.

IF v_shkzg EQ 'H'.

IF bseg-skfbt IS INITIAL.

item-namt = item-namt + ( item-tds * -1 ). "Net Amount

ENDIF.

ELSE.

IF bseg-skfbt IS INITIAL.

item-namt = item-namt + item-tds.

ENDIF.

ELSE.

item-tds = bseg-qbshb. "TDS

ENDIF.

ENDIF. "IF bseg-augbl IS INITIAL AND BSEG-BSCHL EQ '29'.

COLLECT item.

CLEAR item.

ENDSELECT.

ENDFORM. " item_details_29_25

Re: Performance

Former Member — Thu, 18 Oct 2007 11:39:07 GMT

Hi,

There is a lot of scope for performance improvement:

1. SELECT queries should never have ENDSELECT. INstead retrieve all the data in internal table at one go

2. WHERE CLAUSE of the select query should have fields in the same order as they exist in table.

3. Fileds from TABLE should be retrieved in the same order as they exist in Table.

Best regards,

Prashant

Re: Performance

Former Member — Thu, 18 Oct 2007 11:41:55 GMT

Whats the better way to improve this report as developed by others.

Can we change the same or create new one.

Re: Performance

Former Member — Thu, 18 Oct 2007 11:43:11 GMT

Hi,

In the first query,

1. SELECT SINGLE * FROM payr WHERE zbukr = p_bukrs

AND vblnr = p_vblnr

AND gjahr = p_gjahr

AND voidr = '00'.

Instead of SELECT SINGLE *, use select statement with fields to retrive.

2. In the second query,

SELECT shkzg skfbt dmbtr wskto belnr gjahr bukrs qbshb lifnr bschl nebtr

FROM bseg INTO TABLE ibseg WHERE augbl = p_vblnr AND bukrs = p_bukrs

AND koart = 'K' AND auggj = p_gjahr AND belnr NE p_vblnr.

To retrieve from BSEG table, it takes more time. So pass all the keyfields to retrieve records. Then it will be working fine. Once u finetune this query, this report will work fine.

3. Before the third query,

LOOP AT ibseg.

Net Amount
If Amount is Credited

IF ibseg-shkzg EQ 'H'.

use delete statement to delete where ibseg-shkzg ne 'H'.

DELETE IBSEG where ibseg-shkzg ne 'H'.

Then use the following statement.

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

Likewise, try for the remaining.

Re: Performance

Former Member — Thu, 18 Oct 2007 11:44:54 GMT

You are writing so many select statements to BSEG which is not good

and in the where condition give the fey fields first and then other fields

why can't you check the tables BSIK and BSAK fields instead of BSEG and BKPF tables which also holds good the data of Vendor payments

see the doc

1) Dont use nested select statements

2) If possible use for all entries in addition

3) In the where addition make sure you give all the primary key

4) Use Index for the selection criteria.

5) You can also use inner joins

6) You can try to put the data from the first select statement into an Itab and then in order to select the data from the second table use for all entries in.

7) Use the runtime analysis SE30 and SQL Trace (ST05) to identify the performance and also to identify where the load is heavy, so that you can change the code accordingly

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/5d0db4c9-0e01-0010-b68f-9b1408d5f234

ABAP performance depends upon various factors and in devicded in three parts:

1. Database

2. ABAP

3. System

Run Any program using SE30 (performance analys) to improve performance refer to tips and trics section of SE30, Always remember that ABAP perfirmance is improved when there is least load on Database.

u can get an interactive grap in SE30 regarding this with a file.

also if u find runtime of parts of codes then use :

Switch on RTA Dynamically within ABAP Code

*To turn runtim analysis on within ABAP code insert the following code

SET RUN TIME ANALYZER ON.

*To turn runtim analysis off within ABAP code insert the following code

SET RUN TIME ANALYZER OFF.

Always check the driver internal tables is not empty, while using FOR ALL ENTRIES

Avoid for all entries in JOINS

Try to avoid joins and use FOR ALL ENTRIES.

Try to restrict the joins to 1 level only ie only for tables

Avoid using Select *.

Avoid having multiple Selects from the same table in the same object.

Try to minimize the number of variables to save memory.

The sequence of fields in 'where clause' must be as per primary/secondary index ( if any)

Avoid creation of index as far as possible

Avoid operators like <>, > , < & like % in where clause conditions

Avoid select/select single statements in loops.

Try to use 'binary search' in READ internal table. Ensure table is sorted before using BINARY SEARCH.

Avoid using aggregate functions (SUM, MAX etc) in selects ( GROUP BY , HAVING,)

Avoid using ORDER BY in selects

Avoid Nested Selects

Avoid Nested Loops of Internal Tables

Try to use FIELD SYMBOLS.

Try to avoid into Corresponding Fields of

Avoid using Select Distinct, Use DELETE ADJACENT

Check the following Links

http://www.sapgenie.com/abap/performance.htm

http://www.thespot4sap.com/Articles/SAPABAPPerformanceTuning_PerformanceAnalysisTools.asp

check the below link

http://www.sap-img.com/abap/performance-tuning-for-data-selection-statement.htm

See the following link if it's any help:

http://www.thespot4sap.com/Articles/SAPABAPPerformanceTuning_PerformanceAnalysisTools.asp

Check also http://service.sap.com/performance

and

books like

http://www.sap-press.com/product.cfm?account=&product=H951

http://www.sap-press.com/product.cfm?account=&product=H973

http://www.sap-img.com/abap/more-than-100-abap-interview-faqs.htm

http://www.thespot4sap.com/Articles/SAPABAPPerformanceTuning_PerformanceAnalysisTools.asp

Performance tuning for Data Selection Statement

http://www.sap-img.com/abap/performance-tuning-for-data-selection-statement.htm

Debugger

http://help.sap.com/saphelp_47x200/helpdata/en/c6/617ca9e68c11d2b2ab080009b43351/content.htm

http://www.cba.nau.edu/haney-j/CIS497/Assignments/Debugging.doc

http://help.sap.com/saphelp_erp2005/helpdata/en/b3/d322540c3beb4ba53795784eebb680/frameset.htm

Run Time Analyser

http://help.sap.com/saphelp_47x200/helpdata/en/c6/617cafe68c11d2b2ab080009b43351/content.htm

SQL trace

http://help.sap.com/saphelp_47x200/helpdata/en/d1/801f7c454211d189710000e8322d00/content.htm

CATT - Computer Aided Testing Too

http://help.sap.com/saphelp_47x200/helpdata/en/b3/410b37233f7c6fe10000009b38f936/frameset.htm

Test Workbench

http://help.sap.com/saphelp_47x200/helpdata/en/a8/157235d0fa8742e10000009b38f889/frameset.htm

Coverage Analyser

http://help.sap.com/saphelp_47x200/helpdata/en/c7/af9a79061a11d4b3d4080009b43351/content.htm

Runtime Monitor

http://help.sap.com/saphelp_47x200/helpdata/en/b5/fa121cc15911d5993d00508b6b8b11/content.htm

Memory Inspector

http://help.sap.com/saphelp_47x200/helpdata/en/a2/e5fc84cc87964cb2c29f584152d74e/content.htm

ECATT - Extended Computer Aided testing tool.

http://help.sap.com/saphelp_47x200/helpdata/en/20/e81c3b84e65e7be10000000a11402f/frameset.htm

Just refer to these links...

You can go to the transaction SE30 to have the runtime analysis of your program.Also try the transaction SCI , which is SAP Code Inspector.

1 Always check the driver internal tables is not empty, while using FOR ALL ENTRIES

2 Avoid for all entries in JOINS

3 Try to avoid joins and use FOR ALL ENTRIES.

4 Try to restrict the joins to 1 level only ie only for 2 tables

5 Avoid using Select *.

6 Avoid having multiple Selects from the same table in the same object.

7 Try to minimize the number of variables to save memory.

8 The sequence of fields in 'where clause' must be as per primary/secondary index ( if any)

9 Avoid creation of index as far as possible

10 Avoid operators like <>, > , < & like % in where clause conditions

11 Avoid select/select single statements in loops.

12 Try to use 'binary search' in READ internal table. Ensure table is sorted before using BINARY SEARCH.

13 Avoid using aggregate functions (SUM, MAX etc) in selects ( GROUP BY , HAVING,)

14 Avoid using ORDER BY in selects

15 Avoid Nested Selects

16 Avoid Nested Loops of Internal Tables

17 Try to use FIELD SYMBOLS.

18 Try to avoid into Corresponding Fields of

19 Avoid using Select Distinct, Use DELETE ADJACENT.

Regards

Anji

Re: Performance

Former Member — Thu, 18 Oct 2007 11:53:25 GMT

Hi,

<a href="http://www.saptechnical.com/Tutorials/ABAP/PerformanceSTDs.htm">ABAP Performance</a>

reward if useful.

Re: Performance

Former Member — Thu, 18 Oct 2007 11:59:23 GMT

There is no need to create a new report. Just fine tune the queries as I have mentioned. Do for all the queries. Ur problem will be solved.

Re: Performance

Former Member — Thu, 18 Oct 2007 13:55:34 GMT

Run this through ST05. this will show you where the database bottlenecks are.

Rob