topic Re: Performance Tunning in Application Development and Automation Discussions

Performance Tunning

Former Member — Mon, 31 Dec 2007 09:32:04 GMT

Hi Friends,

I have an ZREPORT in SD, its taking long time during execution, so in T-code : SE30 i had done its run time analysis the readings where,

ABAP -

85 %

DB -

10 %

system---- 5 %,

i also done the sql trace in ST05 , its showing maximum time taken for the select statement given below ( down )

SELECT a_{vbeln a}auart a~audat

a_{kunnr a}bstnk a~bstdk

a_{vtweg a}vkorg a~knumv

a_{waerk b}werks b~pstyv

b_{posnr b}matnr b~kwmeng

b_{netpr c}spart c~extwg

c_{ferth c}mtart d~maktx

h_{lfsta h}lfgsa e~kdgrp

f_{name1 f}ort01 g~country

j~sort1

INTO CORRESPONDING FIELDS OF TABLE it_result

FROM vbak AS a

INNER JOIN vbap AS b ON a_{vbeln = b}vbeln

INNER JOIN mara AS c ON c_{matnr = b}matnr

INNER JOIN makt AS d ON d_{matnr = b}matnr

INNER JOIN knvv AS e ON e_{kunnr = a}kunnr

INNER JOIN kna1 AS f ON f_{kunnr = e}kunnr

INNER JOIN adrc AS g ON g_{ADDRNUMBER = f}adrnr

INNER JOIN vbup AS h ON b_{vbeln = h}vbeln AND

b_{posnr = h}posnr

INNER JOIN t001w AS i ON b_{werks = i}werks

INNER JOIN adrc AS j ON i_{adrnr = j}addrnumber

WHERE a~vkorg EQ pr_vkorg AND

a~vtweg EQ pr_vtweg AND

a~auart IN so_auart AND

a~audat IN so_audat AND

a~vbeln IN so_vbeln AND

b~werks IN so_werks AND

b~abgru EQ ' ' AND

a~kunnr IN so_kunnr AND

f~name1 IN so_name1 AND

e~kdgrp IN so_kdgrp AND

g~country IN so_ctry AND

b~matnr IN so_matnr AND

d~maktx IN so_maktx AND

c~spart IN so_spart AND

c~ferth IN so_ferth AND

c~mtart IN so_mtart.

i have also created an INDEX for table VBAK for the fields used in these select statement.

SO what should i do to improve the analysis,,,,, and what should be the normal readings of ABAP DB and system values in SE30.

or should i change the select satatement

Regards

Kumar M

Re: Performance Tunning

Former Member — Mon, 31 Dec 2007 09:56:02 GMT

Hi Mukesh,

I can see why the performance is an issue in your code.

First of all you should not use more than 5 INNER JOINS in any select querry. Inner Joins are not always a better solution.

What you can do is create separate internal table for Selection from ADRC,T001W, KNA1, VBUP and use For all entries option with Inner join.

The Idea is to select only those records which are required and get rid of unneccary selection.

This should increase the performance of your code.

sample code:

*---SELECTION MADE FOR REJECT QUANTITY

SELECT T5~MBLNR

T5~MJAHR

T6~MATNR

T6~MENGE

INTO TABLE I_MSEG

FROM MKPF AS T5 INNER JOIN MSEG AS T6

ON T5_{MBLNR = T6}MBLNR AND

T5_{MJAHR = T6}MJAHR FOR ALL ENTRIES IN I_ITAB1

WHERE T5~BUDAT IN S_START AND

T6~MATNR = I_ITAB1-MATNR AND

T6~BWART = C_BWART AND

T6~AUFNR = I_ITAB1-AUFNR.

ENDIF.

<b>Reward points if this helps.

Manish</b>

Re: Performance Tunning

Former Member — Tue, 01 Jan 2008 04:11:38 GMT

Mukesh,

Your select statement has way too many inner joins. Try using the following code.

TYPES: BEGIN OF ty_vbak,

vbeln TYPE vbak-vbeln,

auart TYPE vbak-auart,

audat TYPE vbak-audat,

kunnr TYPE vbak-kunnr,

bstnk TYPE vbak-bstnk,

bstdk TYPE vbak-bstdk,

vtweg TYPE vbak-vtweg,

vkorg TYPE vbak-vkorg,

spart TYPE vbak-spart,

knumv TYPE vbak-knumv,

waerk TYPE vbak-waerk,

END OF ty_vbak,

BEGIN OF ty_vbap,

vbeln TYPE vbap-vbeln ,

posnr TYPE vbap-posnr ,

werks TYPE vbap-werks ,

pstyv TYPE vbap-pstyv ,

matnr TYPE vbap-matnr ,

kwmeng TYPE vbap-kwmeng,

netpr type vbap-netpr ,

END OF ty_vbap,

BEGIN OF ty_mara,

matnr TYPE mara-matnr,

spart TYPE mara-spart,

extwg TYPE mara-extwg,

ferth TYPE mara-ferth,

mtart TYPE mara-mtart,

END OF ty_mara,

BEGIN OF ty_makt,

matnr TYPE makt-matnr,

maktx TYPE makt-maktx,

END OF ty_makt,

BEGIN OF ty_knvv,

kunnr TYPE knvv-kunnr,

vkorg TYPE knvv-vkorg,

vtweg TYPE knvv-vtweg,

spart TYPE knvv-spart,

kdgrp TYPE knvv-kdgrp,

END OF ty_knvv,

BEGIN OF ty_kna1,

kunnr TYPE kna1-kunnr,

name1 TYPE kna1-name1,

ort01 TYPE kna1-ort01,

adrnr TYPE kna1-adrnr,

END OF ty_kna1,

BEGIN OF ty_adrc,

addrnumber TYPE adrc-addrnumber,

country TYPE adrc-country ,

sort1 TYPE adrc-sort1 ,

END OF ty_adrc,

BEGIN OF ty_vbup,

vbeln TYPE vbup-vbeln,

posnr TYPE vbup-posnr,

lfsta TYPE vbup-lfsta,

lfgsa TYPE vbup-lfgsa,

END OF ty_vbup,

BEGIN OF ty_result,

vbeln TYPE vbak-vbeln ,

auart TYPE vbak-auart ,

audat TYPE vbak-audat ,

kunnr TYPE vbak-kunnr ,

bstnk TYPE vbak-bstnk ,

bstdk TYPE vbak-bstdk ,

vtweg TYPE vbak-vtweg ,

vkorg TYPE vbak-vkorg ,

knumv TYPE vbak-knumv ,

waerk TYPE vbak-waerk ,

werks TYPE vbap-werks ,

pstyv TYPE vbap-pstyv ,

posnr TYPE vbap-posnr ,

matnr TYPE vbap-matnr ,

kwmeng TYPE vbap-kwmeng ,

netpr TYPE vbap-netpr ,

spart TYPE mara-spart ,

extwg TYPE mara-extwg ,

ferth TYPE mara-ferth ,

mtart TYPE mara-mtart ,

maktx TYPE makt-maktx ,

lfsta TYPE vbup-lfsta ,

lfgsa TYPE vbup-lfgsa ,

kdgrp TYPE knvv-kdgrp ,

name1 TYPE kna1-name1 ,

ort01 TYPE kna1-ort01 ,

country TYPE adrc-country,

sort1 TYPE adrc-sort1 ,

END OF ty_result.

DATA: w_vbap TYPE ty_vbap ,

w_vbak TYPE ty_vbak ,

w_mara TYPE ty_mara ,

w_makt TYPE ty_makt ,

w_knvv TYPE ty_knvv ,

w_kna1 TYPE ty_kna1 ,

w_adrc TYPE ty_adrc ,

w_vbup TYPE ty_vbup ,

w_result TYPE ty_result,

t_vbak TYPE HASHED TABLE OF ty_vbak

WITH UNIQUE KEY vbeln,

t_vbak_tmp TYPE TABLE OF ty_vbak,

t_vbap TYPE TABLE OF ty_vbap,

t_vbap_tmp TYPE TABLE OF ty_vbap,

t_mara TYPE HASHED TABLE OF ty_mara

WITH UNIQUE KEY matnr,

t_makt TYPE HASHED TABLE OF ty_makt

WITH UNIQUE KEY matnr,

t_knvv TYPE HASHED TABLE OF ty_knvv

WITH UNIQUE KEY kunnr vkorg vtweg spart,

t_kna1 TYPE HASHED TABLE OF ty_kna1

WITH UNIQUE KEY kunnr,

t_kna1_tmp TYPE TABLE OF ty_kna1,

t_adrc TYPE SORTED TABLE OF ty_adrc

WITH NON-UNIQUE KEY addrnumber,

t_vbup TYPE HASHED TABLE OF ty_vbup

WITH UNIQUE KEY vbeln posnr,

t_result TYPE TABLE OF ty_result.

SELECT vbeln

auart

audat

kunnr

bstnk

bstdk

vtweg

vkorg

spart

knumv

waerk

FROM vbak

INTO TABLE t_vbak

WHERE vbeln IN so_vbeln

AND audat IN so_audat

AND auart IN so_auart

AND vkorg EQ pr_vkorg

AND vtweg EQ pr_vtweg

AND kunnr IN so_kunnr.

IF sy-subrc EQ 0.

SELECT vbeln

posnr

werks

pstyv

matnr

kwmeng

netpr

FROM vbap

INTO TABLE t_vbap

FOR ALL ENTRIES IN t_vbak

WHERE vbeln EQ t_vbak-vbeln

AND abgru EQ space

AND werks IN so_werks.

IF sy-subrc EQ 0.

SELECT vbeln

posnr

lfsta

lfgsa

FROM vbup

INTO TABLE t_vbup

FOR ALL ENTRIES IN t_vbap

WHERE vbeln EQ t_vbap-vbeln

AND posnr EQ t_vbap-posnr.

t_vbap_tmp[] = t_vbap[].

SORT t_vbap_tmp BY matnr.

DELETE ADJACENT DUPLICATES FROM t_vbap_tmp COMPARING matnr.

SELECT matnr

spart

extwg

ferth

mtart

FROM mara

INTO TABLE t_mara

FOR ALL ENTRIES IN t_vbap_tmp

WHERE matnr EQ t_vbap_tmp-matnr

AND mtart IN so_mtart

AND ferth IN so_ferth

AND spart IN so_spart.

IF sy-subrc EQ 0.

SELECT matnr

maktx

FROM makt

INTO TABLE t_makt

FOR ALL ENTRIES IN t_mara

WHERE matnr EQ t_mara-matnr

AND spras EQ sy-langu.

ENDIF.

t_vbak_tmp[] = t_vbak[].

SORT t_vbak_tmp BY kunnr vkorg vtweg spart.

DELETE ADJACENT DUPLICATES FROM t_vbak_tmp

COMPARING kunnr vkorg vtweg spart.

SELECT kunnr

vkorg

vtweg

spart

kdgrp

FROM knvv

INTO TABLE t_knvv

FOR ALL ENTRIES IN t_vbak_tmp

WHERE kunnr EQ t_vbak_tmp-kunnr

AND vkorg EQ t_vbak_tmp-vkorg

AND vtweg EQ t_vbak_tmp-vtweg

AND spart EQ t_vbak_tmp-spart

AND kdgrp IN so_kdgrp.

DELETE ADJACENT DUPLICATES FROM t_vbak_tmp COMPARING kunnr.

SELECT kunnr

name1

ort01

adrnr

FROM kna1

INTO TABLE t_kna1

FOR ALL ENTRIES IN t_vbak_tmp

WHERE kunnr EQ t_vbak_tmp-kunnr

AND name1 IN so_name1.

IF sy-subrc EQ 0.

t_kna1_tmp[] = t_kna1[].

SORT t_kna1_tmp BY adrnr.

DELETE ADJACENT DUPLICATES FROM t_kna1_tmp COMPARING adrnr.

SELECT addrnumber

country

sort1

FROM adrc

INTO TABLE t_adrc

FOR ALL ENTRIES IN t_kna1_tmp

WHERE addrnumber EQ t_kna1_tmp-adrnr

AND date_from LE sy-datum

AND date_to GE sy-datum

AND country IN so_ctry.

ENDIF.

REFRESH t_result.

LOOP AT t_vbap INTO w_vbap.

READ TABLE t_vbak INTO w_vbak

WITH KEY vbeln = w_vbap-vbeln

TRANSPORTING

auart

audat

kunnr

bstnk

bstdk

vtweg

vkorg

spart

knumv

waerk.

CHECK sy-subrc EQ 0.

READ TABLE t_mara INTO w_mara

WITH KEY matnr = w_vbap-matnr

TRANSPORTING

spart

extwg

ferth

mtart.

CHECK sy-subrc EQ 0.

READ TABLE t_makt INTO w_makt

WITH KEY matnr = w_vbap-matnr

TRANSPORTING

maktx.

CHECK sy-subrc EQ 0.

READ TABLE t_knvv INTO w_knvv

WITH KEY kunnr = w_vbak-kunnr

vkorg = w_vbak-vkorg

vtweg = w_vbak-vtweg

spart = w_vbak-spart

TRANSPORTING

kdgrp.

CHECK sy-subrc EQ 0.

READ TABLE t_kna1 INTO w_kna1

WITH KEY kunnr = w_vbak-kunnr

TRANSPORTING

name1

ort01

adrnr.

CHECK sy-subrc EQ 0.

READ TABLE t_adrc INTO w_adrc

WITH KEY addrnumber = w_kna1-adrnr

TRANSPORTING

country

sort1.

CHECK sy-subrc EQ 0.

READ TABLE t_vbup INTO w_vbup

WITH KEY vbeln = w_vbap-vbeln

posnr = w_vbap-posnr

TRANSPORTING

lfsta

lfgsa.

CHECK sy-subrc EQ 0.

w_result-vbeln = w_vbak-vbeln .

w_result-auart = w_vbak-auart .

w_result-audat = w_vbak-audat .

w_result-kunnr = w_vbak-kunnr .

w_result-bstnk = w_vbak-bstnk .

w_result-bstdk = w_vbak-bstdk .

w_result-vtweg = w_vbak-vtweg .

w_result-vkorg = w_vbak-vkorg .

w_result-knumv = w_vbak-knumv .

w_result-waerk = w_vbak-waerk .

w_result-werks = w_vbap-werks .

w_result-pstyv = w_vbap-pstyv .

w_result-posnr = w_vbap-posnr .

w_result-matnr = w_vbap-matnr .

w_result-kwmeng = w_vbap-kwmeng .

w_result-netpr = w_vbap-netpr .

w_result-spart = w_mara-spart .

w_result-extwg = w_mara-extwg .

w_result-ferth = w_mara-ferth .

w_result-mtart = w_mara-mtart .

w_result-maktx = w_makt-maktx .

w_result-lfsta = w_vbup-lfsta .

w_result-lfgsa = w_vbup-lfgsa .

w_result-kdgrp = w_knvv-kdgrp .

w_result-name1 = w_kna1-name1 .

w_result-ort01 = w_kna1-ort01 .

w_result-country = w_adrc-country.

w_result-sort1 = w_adrc-sort1 .

APPEND w_result TO t_result.

CLEAR w_result.

ENDLOOP.

Re: Performance Tunning

Former Member — Tue, 01 Jan 2008 05:17:27 GMT

hi mukesh,

Main problem in ur code is using inner joins for more than 2 tables.It is advisable to use for all entries for more that 2 tables.Follow the sample code and make nessesry changes in ur code.

CONSTANTS: c_act_plan(2) TYPE c VALUE 'U1' ,

c_person(1) TYPE c VALUE 'P' ,

p1_betid(8) TYPE c VALUE '50005316',

c_topdown(1) TYPE c VALUE 'A' ,

c_admnby(3) TYPE c VALUE '032' ,

c_bet_type(1) TYPE c VALUE 'Q' ,

c_firmed(1) TYPE c VALUE '1' ,

c_subtyp_mail(4) TYPE c VALUE '0001' .

TYPES: BEGIN OF ty_hrp1001,

objid TYPE hrp1001-objid,

END OF ty_hrp1001,

BEGIN OF ty_pa0001,

pernr TYPE pa0001-pernr,

subty TYPE pa0001-subty,

objps TYPE pa0001-objps,

sprps TYPE pa0001-sprps,

endda TYPE pa0001-endda,

begda TYPE pa0001-begda,

seqnr TYPE pa0001-seqnr,

werks TYPE pa0001-werks,

gsber TYPE pa0001-gsber,

btrtl TYPE pa0001-btrtl,

END OF ty_pa0001,

BEGIN OF ty_t500p,

persa TYPE t500p-persa,

name1 TYPE t500p-name1,

END OF ty_t500p,

BEGIN OF ty_t001p,

werks TYPE t001p-werks,

btrtl TYPE t001p-btrtl,

btext TYPE t001p-btext,

END OF ty_t001p,

BEGIN OF ty_pa0002,

pernr TYPE pa0002-pernr,

subty TYPE pa0002-subty,

objps TYPE pa0002-objps,

sprps TYPE pa0002-sprps,

endda TYPE pa0002-endda,

begda TYPE pa0002-begda,

seqnr TYPE pa0002-seqnr,

nachn TYPE pa0002-nachn,

vorna TYPE pa0002-vorna,

END OF ty_pa0002,

BEGIN OF ty_pa0105,

pernr TYPE pa0105-pernr ,

subty TYPE pa0105-subty ,

objps TYPE pa0105-objps ,

sprps TYPE pa0105-sprps ,

endda TYPE pa0105-endda ,

begda TYPE pa0105-begda ,

seqnr TYPE pa0105-seqnr ,

usrid_long TYPE pa0105-usrid_long,

END OF ty_pa0105,

BEGIN OF ty_output,

pernr TYPE pa0002-pernr ,

werks TYPE pa0001-werks ,

gsber TYPE pa0001-gsber ,

btrtl TYPE pa0001-btrtl ,

name1 TYPE t500p-name1 ,

btext TYPE t001p-btext ,

nachn TYPE pa0002-nachn ,

vorna TYPE pa0002-vorna ,

usrid_long TYPE pa0105-usrid_long,

END OF ty_output.

DATA: w_hrp1001 TYPE ty_hrp1001,

t_hrp1001 TYPE TABLE OF ty_hrp1001,

t_hrp1001_tmp TYPE TABLE OF ty_hrp1001,

w_pa0001 TYPE ty_pa0001 ,

t_pa0001 TYPE SORTED TABLE OF ty_pa0001

WITH NON-UNIQUE KEY pernr ,

t_pa0001_tmp TYPE TABLE OF ty_pa0001 ,

w_t500p TYPE ty_t500p ,

t_t500p TYPE HASHED TABLE OF ty_t500p

WITH UNIQUE KEY persa ,

w_t001p TYPE ty_t001p ,

t_t001p TYPE HASHED TABLE OF ty_t001p

WITH UNIQUE KEY werks btrtl ,

w_pa0002 TYPE ty_pa0002 ,

t_pa0002 TYPE SORTED TABLE OF ty_pa0002

WITH NON-UNIQUE KEY pernr ,

t_pa0002_tmp TYPE TABLE OF ty_pa0002 ,

w_pa0105 TYPE ty_pa0105 ,

t_pa0105 TYPE SORTED TABLE OF ty_pa0105

WITH NON-UNIQUE KEY pernr ,

w_output TYPE ty_output ,

t_output TYPE TABLE OF ty_output .

SELECT objid

FROM hrp1001

INTO TABLE t_hrp1001

WHERE otype EQ c_person

AND plvar EQ c_act_plan

AND rsign EQ c_topdown

AND relat EQ c_admnby

AND istat EQ c_firmed

AND begda LE sy-datum

AND endda GT sy-datum

AND sclas EQ c_bet_type

AND sobid EQ p1_betid.

IF sy-subrc EQ 0.

SORT t_hrp1001 BY objid.

ENDIF.

IF NOT t_hrp1001[] IS INITIAL.

t_hrp1001_tmp[] = t_hrp1001[].

DELETE ADJACENT DUPLICATES FROM t_hrp1001_tmp

COMPARING objid.

SELECT pernr

subty

objps

sprps

endda

begda

seqnr

werks

gsber

btrtl

FROM pa0001

INTO TABLE t_pa0001

FOR ALL ENTRIES IN t_hrp1001_tmp

WHERE pernr EQ t_hrp1001_tmp-objid

AND endda GT sy-datum

AND begda LE sy-datum.

SELECT pernr

subty

objps

sprps

endda

begda

seqnr

nachn

vorna

FROM pa0002

INTO TABLE t_pa0002

FOR ALL ENTRIES IN t_hrp1001_tmp

WHERE pernr EQ t_hrp1001_tmp-objid

AND endda GT sy-datum

AND begda LE sy-datum.

ENDIF.

IF NOT t_pa0001[] IS INITIAL.

t_pa0001_tmp[] = t_pa0001[].

SORT t_pa0001_tmp BY werks btrtl.

DELETE ADJACENT DUPLICATES FROM t_pa0001_tmp COMPARING werks btrtl.

SELECT werks

btrtl

btext

FROM t001p

INTO TABLE t_t001p

FOR ALL ENTRIES IN t_pa0001_tmp

WHERE werks EQ t_pa0001_tmp-werks

AND btrtl EQ t_pa0001_tmp-btrtl.

DELETE ADJACENT DUPLICATES FROM t_pa0001_tmp COMPARING werks.

SELECT persa

name1

FROM t500p

INTO TABLE t_t500p

FOR ALL ENTRIES IN t_pa0001_tmp

WHERE persa EQ t_pa0001_tmp-werks.

ENDIF.

IF NOT t_pa0002[] IS INITIAL.

t_pa0002_tmp[] = t_pa0002[].

DELETE ADJACENT DUPLICATES FROM t_pa0002_tmp COMPARING pernr.

SELECT pernr

subty

objps

sprps

endda

begda

seqnr

usrid_long

FROM pa0105

INTO TABLE t_pa0105

FOR ALL ENTRIES IN t_pa0002_tmp

WHERE pernr EQ t_pa0002_tmp-pernr

AND subty EQ c_subtyp_mail

AND endda GT sy-datum

AND begda LE sy-datum

AND usrty EQ c_subtyp_mail.

ENDIF.

REFRESH t_output.

LOOP AT t_hrp1001 INTO w_hrp1001.

LOOP AT t_pa0001 INTO w_pa0001

WHERE pernr EQ w_hrp1001-objid.

READ TABLE t_t500p INTO w_t500p WITH KEY persa = w_pa0001-werks

TRANSPORTING

name1.

CHECK sy-subrc EQ 0.

READ TABLE t_t001p INTO w_t001p WITH KEY werks = w_pa0001-werks

btrtl = w_pa0001-btrtl

TRANSPORTING

btext.

LOOP AT t_pa0002 INTO w_pa0002

WHERE pernr EQ w_hrp1001-objid.

LOOP AT t_pa0105 INTO w_pa0105

WHERE pernr EQ w_pa0002-pernr.

w_output-pernr = w_pa0002-pernr .

w_output-werks = w_pa0001-werks .

w_output-gsber = w_pa0001-gsber .

w_output-btrtl = w_pa0001-btrtl .

w_output-name1 = w_t500p-name1 .

w_output-btext = w_t001p-btext .

w_output-nachn = w_pa0002-nachn .

w_output-vorna = w_pa0002-vorna .

w_output-usrid_long = w_pa0105-usrid_long.

APPEND w_output TO t_output.

CLEAR w_output.

ENDLOOP.

reward if it is usful,

Thanks,

Srikanth.A

Re: Performance Tunning

Former Member — Tue, 01 Jan 2008 05:35:26 GMT

Hi Mark,

That was good explanation,, i really say that you have a lot of patience, u have rewritten the whole the code.

Thanks for the reply.

awarded the maximum points.

Regards

Mukesh