topic Re: Performance Issue in Application Development and Automation Discussions

Performance Issue

Former Member — Wed, 21 Nov 2007 09:39:56 GMT

Hi Frnds ,

Im Having one internal table tl_soline1 .which fetches data from vbak,vbap,vbek.

for this im using inner joins .

But it is giving Performance problem . For executing it is taking some much time.

please Look in to the code once & give me the Solutions.

TYPES : BEGIN OF tp_soline1,

vbeln TYPE vbak-vbeln, "Order ID(10)

erdat TYPE erdat, "MOD43

ernam TYPE ernam, "MOD43

auart TYPE vbak-auart, "Sales type

lifsk TYPE vbak-lifsk, "Delivery block

faksk TYPE vbak-faksk, "Billing block

netwr TYPE vbak-netwr, "Order Net value

vtweg TYPE vtweg ,

vdatu TYPE vbak-vdatu, "Requested del. date

bstnk TYPE vbak-bstnk, "PO Number

bstdk TYPE vbak-bstdk, "PO Date

kunnr TYPE vbak-kunnr, "Customer (soldtoparty)

zz_prime TYPE zzprime,

zz_dpas TYPE zzdpas,

posnr TYPE vbap-posnr, "Item(6)

matnr TYPE vbap-matnr, "Part Name(18)

kdmat TYPE matnr_ku,

kwmeng TYPE vbap-kwmeng,"Order Quantity(13)

werks TYPE vbap-werks, "Site

vstel TYPE vbap-vstel, "Site(4)

netpr TYPE vbap-netpr, "UnitSelling Price(11)

mvgr1 TYPE mvgr1,

mvgr2 TYPE mvgr2,

mvgr3 TYPE mvgr3,

mvgr4 TYPE mvgr4,

mvgr5 TYPE mvgr5,

ps_psp_pnr TYPE vbap-ps_psp_pnr , "WBS Element

bedae TYPE bedae,

adpri TYPE vbap-adpri, "Order priority

zz_clin TYPE vbap-zz_clin, "Customer contract line item

etenr TYPE vbep-etenr, "Schedule Line

ettyp TYPE vbep-ettyp, "Schedule Line category

lfrel TYPE vbep-lfrel,

edatu TYPE vbep-edatu, "Due date

wmeng TYPE vbep-wmeng, "Ord quant in sales units

lmeng TYPE vbep-lmeng, "Shipped Quantity

mbdat TYPE vbep-mbdat, "Material availability date

END OF tp_soline1.

DATA : tl_soline1 TYPE STANDARD TABLE OF tp_soline1

WITH HEADER LINE,

SELECT vbak_{vbeln vbak}erdat

vbak_{ernam vbak}auart

vbak_{lifsk vbak}faksk

vbak_{netwr vbak}vtweg

vbak_{vdatu vbak}bstnk

vbak_{bstdk vbak}kunnr

vbak_{zz_prime vbak}zz_dpas

vbap_{posnr vbap}matnr

vbap_{kdmat vbap}kwmeng

vbap_{werks vbap}vstel

vbap_{netpr vbap}mvgr1

vbap_{mvgr2 vbap}mvgr3

vbap_{mvgr4 vbap}mvgr5

vbap_{ps_psp_pnr vbap}bedae

vbap_{adpri vbap}zz_clin

vbep_{etenr vbep}ettyp

vbep_{lfrel vbep}edatu

vbep_{wmeng vbep}lmeng

vbep~mbdat

FROM vbak

INNER JOIN vbap

ON vbak_{vbeln = vbap}vbeln

INNER JOIN vbep

ON vbep_{vbeln = vbap}vbeln

AND vbep_{posnr = vbap}posnr

INTO TABLE tl_soline1

WHERE vbak~auart IN s_saletp

AND vbap~werks IN s_lsite

AND vbep~etenr IN r_etenr

AND vbap~matnr IN s_matnr .

Re: Performance Issue

Former Member — Wed, 21 Nov 2007 09:50:32 GMT

AVOID INNER JOINS AND USE FOR ALL ENTRIES

BECAUSE IF YOU USE JOINS FOR 3 OR MORE TABLES THAN THE LOAD ON DATA BASE WILL BE VERY HIGH

IF YOU USE JOINS THEN THE DATA BASE CONNECTION EXIST UP TO THE PROGRAM EXECUTION RESULT IN WHICH THE LOAD ON DATABASE PERFORMANCE WILL REDUCE

IF YOU USE FOR ALL ENTRIES IT ALSO DONES SAME RESULT AS JOINS DONE

ADVANTAGE IS THERE IS NO DATABASE CONNECTION FOR THIS

IT WILL RETRIVE THE DATA FROM DATA BASE TABLE AT ONCE

Re: Performance Issue

Former Member — Wed, 21 Nov 2007 12:25:46 GMT

hi,

avoid using more than 1 inner joins as it effects your program performance.

if it is required to take data from more than 2 tables than use for all entries.

again it depends on ur data contents and ur where clause.

if u r fetching data from header table and item table and if both have got the document number than u can use inner joins but if the item table does not have document no. than for one header data it will give one item detail and for multiple item data it will repeat the header data. thus giving redundant data.

Re: Performance Issue

Former Member — Wed, 21 Nov 2007 14:25:43 GMT

Your problem is that you are not using an index in your SELECT. Using FOR ALL ENTRIES will not improve it.

There is a secondary index table - VAPMA (Sales Index: Order Items by Material) that is indexed by material. You should use that in your SELECT.

Rob