Performance problem

[Former Member]

Hello Gurus,

In a report I have a performance issue. When I do runtime analysis, it says ABAP takes 85 %. Data base 12 %.

Below routine takes maximum time (12 min). Can you suggest possibilities of improvement in it.

FORM select_other_details .

DATA: lf_uprice TYPE konv-kwert,

lf_sdisc TYPE konv-kwert,

lf_bdisc TYPE konv-kwert,

lf_fright1 TYPE konv-kwert,

lf_fright2 TYPE konv-kwert,

lf_bomdisc TYPE konv-kwert.

DATA: lf_bmeng TYPE vbep-bmeng,

lf_webaz LIKE marc-webaz,

lf_plifz LIKE marc-plifz,

lf_loadtg LIKE tvst-loadtg.

REFRESH: gt_vbfa, gt_konv.

IF NOT gt_sail_ord[] IS INITIAL.

SELECT vbelv posnv vbeln posnn vbtyp_n

FROM vbfa

INTO TABLE gt_vbfa

FOR ALL ENTRIES IN gt_sail_ord

WHERE vbelv = gt_sail_ord-vbeln

AND posnv = gt_sail_ord-posnr.

SORT gt_vbfa BY vbelv posnv vbtyp_n.

SELECT knumv kposn kschl kwert kbetr

FROM konv

INTO TABLE gt_konv

FOR ALL ENTRIES IN gt_sail_ord

WHERE knumv = gt_sail_ord-knumv

AND kposn = gt_sail_ord-posnr.

DELETE gt_konv WHERE NOT kschl IN r_kschl.

SELECT vbeln

posnr

etenr

edatu

lifsp

banfn

bmeng

FROM vbep

INTO CORRESPONDING FIELDS OF TABLE gt_vbep

FOR ALL ENTRIES IN gt_sail_ord

WHERE vbeln = gt_sail_ord-vbeln AND

posnr = gt_sail_ord-posnr.

SORT gt_vbep BY vbeln posnr.

SELECT matnr

werks

webaz

plifz

FROM marc

INTO TABLE gt_marc

FOR ALL ENTRIES IN gt_sail_ord

WHERE matnr = gt_sail_ord-matnr AND

werks = gt_sail_ord-werks.

SORT gt_marc BY matnr werks.

REFRESH gt_vstel.

gt_vstel[] = gt_sail_ord[].

SORT gt_vstel BY vstel.

DELETE ADJACENT DUPLICATES FROM gt_vstel COMPARING vstel.

IF NOT gt_vstel[] IS INITIAL.

SELECT vstel

loadtg

FROM tvst

INTO TABLE gt_tvst

FOR ALL ENTRIES IN gt_vstel

WHERE vstel = gt_vstel-vstel.

SORT gt_tvst BY vstel.

ENDIF.

SELECT vbeln

posnr

kalab

FROM mska

INTO TABLE gt_mska

FOR ALL ENTRIES IN gt_sail_ord

WHERE vbeln = gt_sail_ord-vbeln AND

posnr = gt_sail_ord-posnr.

SORT gt_mska BY vbeln posnr.

ENDIF.

IF NOT gt_vbfa[] IS INITIAL.

SELECT vbeln

posnr

lfimg

FROM lips

INTO TABLE gt_lips

FOR ALL ENTRIES IN gt_vbfa

WHERE vbeln = gt_vbfa-vbeln AND

posnr = gt_vbfa-posnn.

SORT gt_lips BY vbeln posnr.

SELECT vbeln

posnr

fkimg

FROM vbrp

INTO TABLE gt_vbrp

FOR ALL ENTRIES IN gt_vbfa

WHERE vbeln = gt_vbfa-vbeln AND

posnr = gt_vbfa-posnn.

SORT gt_vbrp BY vbeln posnr.

SELECT vbeln

fkdat

FROM vbrk

INTO TABLE gt_vbrk

FOR ALL ENTRIES IN gt_vbfa

WHERE vbeln = gt_vbfa-vbeln.

SORT gt_vbrk BY vbeln.

DELETE ADJACENT DUPLICATES FROM gt_vbrk COMPARING vbeln.

ENDIF.

• Performance

• Parallel cursor mechanism

SORT gt_sail_ord BY knumv posnr.

SORT gt_konv BY knumv kposn.

LOOP AT gt_sail_ord INTO wa_sail_ord.

CLEAR: wa_vbfa, lf_bmeng.

**Delivery Quantity

READ TABLE gt_vbfa INTO wa_vbfa WITH KEY

vbelv = wa_sail_ord-vbeln

posnv = wa_sail_ord-posnr

vbtyp_n = 'J'

BINARY SEARCH.

IF sy-subrc = 0.

CLEAR gs_lips.

READ TABLE gt_lips INTO gs_lips

WITH KEY vbeln = wa_vbfa-vbeln

posnr = wa_vbfa-posnn

BINARY SEARCH.

IF sy-subrc = 0.

wa_sail_ord-lfimg = gs_lips-lfimg.

ENDIF.

ENDIF.

CLEAR: wa_vbfa.

READ TABLE gt_vbfa INTO wa_vbfa WITH KEY

vbelv = wa_sail_ord-vbeln

posnv = wa_sail_ord-posnr

vbtyp_n = 'M'

BINARY SEARCH.

IF sy-subrc = 0.

***Billed Quantity

CLEAR gs_vbrp.

READ TABLE gt_vbrp INTO gs_vbrp

WITH KEY vbeln = wa_vbfa-vbeln

posnr = wa_vbfa-posnn

BINARY SEARCH.

IF sy-subrc = 0.

wa_sail_ord-fkimg = gs_vbrp-fkimg.

ENDIF.

***Billing Date

CLEAR gs_vbrk.

READ TABLE gt_vbrk INTO gs_vbrk

WITH KEY vbeln = wa_vbfa-vbeln

BINARY SEARCH.

IF sy-subrc = 0.

wa_sail_ord-fkdat = gs_vbrk-fkdat.

ENDIF.

ENDIF.

**Balance Quantity

wa_sail_ord-balqty = wa_sail_ord-kwmeng -

wa_sail_ord-fkimg.

**customer Request Date

CLEAR gs_vbep.

READ TABLE gt_vbep INTO gs_vbep

WITH KEY vbeln = wa_sail_ord-vbeln

posnr = wa_sail_ord-posnr

BINARY SEARCH.

IF sy-subrc = 0.

wa_sail_ord-edatu = gs_vbep-edatu.

wa_sail_ord-banfn = gs_vbep-banfn.

wa_sail_ord-lifsp = gs_vbep-lifsp.

ENDIF.

**Status

IF wa_sail_ord-fkimg = wa_sail_ord-kwmeng.

wa_sail_ord-stat = 'Completed'.

ELSEIF wa_sail_ord-lfimg = 0 .

wa_sail_ord-stat = 'Open'.

ELSE.

wa_sail_ord-stat = 'Partially Processed'.

ENDIF.

IF wa_sail_ord-abgru <> space.

CLEAR wa_sail_ord-stat.

wa_sail_ord-stat = 'Completed'.

CLEAR wa_abgru_text.

READ TABLE gt_abgru_text INTO wa_abgru_text

WITH KEY abgru = wa_sail_ord-abgru

BINARY SEARCH.

wa_sail_ord-abgru_text = wa_abgru_text-bezei.

ENDIF.

**Confirmation Date

CLEAR gs_vbep.

LOOP AT gt_vbep INTO gs_vbep

WHERE vbeln = wa_sail_ord-vbeln AND

posnr = wa_sail_ord-posnr.

lf_bmeng = lf_bmeng + gs_vbep-bmeng.

wa_sail_ord-cnfdt = gs_vbep-edatu.

ENDLOOP.

• Performance !!!

DELETE gt_vbep WHERE vbeln = wa_sail_ord-vbeln AND

posnr = wa_sail_ord-posnr.

**if confirmation quantity is 0 no need to display

**the confirmation date.

IF lf_bmeng IS INITIAL.

CLEAR wa_sail_ord-cnfdt.

ENDIF.

**Promised Date

IF wa_sail_ord-cnfdt IS INITIAL OR

lf_bmeng NE wa_sail_ord-kwmeng.

wa_sail_ord-vdatu = wa_sail_ord-/rbc2/yv_prodat.

ENDIF.

CLEAR: lf_uprice, lf_sdisc, lf_bdisc,

lf_fright1, lf_fright2, wa_konv,

lf_bomdisc.

LOOP AT gt_konv INTO wa_konv

FROM gf_index1

WHERE knumv = wa_sail_ord-knumv

AND kposn = wa_sail_ord-posnr.

CLEAR gf_index1.

gf_index1 = sy-tabix.

IF wa_konv-kschl = 'ZPSP' OR

wa_konv-kschl = 'ZOSP' OR

wa_konv-kschl = 'ZPNN' OR

wa_konv-kschl = 'ZPNP' OR

wa_konv-kschl = 'ZDOO'.

IF wa_sail_ord-waerk = 'JPY'.

wa_konv-kwert = wa_konv-kwert * 100.

ENDIF.

lf_uprice = lf_uprice + wa_konv-kwert.

ENDIF.

IF wa_konv-kschl = 'ZBOM'.

lf_bomdisc = wa_konv-kwert.

IF wa_sail_ord-waerk = 'JPY'.

lf_bomdisc = lf_bomdisc * 100.

ENDIF.

ENDIF.

IF wa_konv-kschl = 'ZSPD'.

lf_sdisc = wa_konv-kwert.

IF wa_sail_ord-waerk = 'JPY'.

lf_sdisc = lf_sdisc * 100.

ENDIF.

ENDIF.

IF wa_konv-kschl = 'ZBOO'.

lf_bdisc = wa_konv-kwert.

IF wa_sail_ord-waerk = 'JPY'.

lf_bdisc = lf_bdisc * 100.

ENDIF.

ENDIF.

IF wa_konv-kschl = 'ZF01'.

lf_fright1 = wa_konv-kwert.

IF wa_sail_ord-waerk = 'JPY'.

lf_fright1 = lf_fright1 * 100.

ENDIF.

ENDIF.

IF wa_konv-kschl = 'ZF02'.

lf_fright2 = wa_konv-kwert.

IF wa_sail_ord-waerk = 'JPY'.

lf_fright2 = lf_fright2 * 100.

ENDIF.

ENDIF.

ENDLOOP.

***Unit Price

IF wa_sail_ord-kwmeng IS NOT INITIAL.

wa_sail_ord-u_price = lf_uprice / wa_sail_ord-kwmeng .

ENDIF.

IF wa_sail_ord-waerk = 'JPY'.

PERFORM round USING wa_sail_ord-u_price gf_price.

ENDIF.

***Special Discount

IF wa_sail_ord-kwmeng IS NOT INITIAL.

wa_sail_ord-s_disc = ( lf_sdisc / wa_sail_ord-kwmeng ) *

wa_sail_ord-balqty + lf_bomdisc.

IF wa_sail_ord-waerk = 'JPY'.

PERFORM round USING wa_sail_ord-s_disc gf_price.

ENDIF.

***Bonus Discount

wa_sail_ord-b_disc = ( lf_bdisc / wa_sail_ord-kwmeng ) *

wa_sail_ord-balqty .

IF wa_sail_ord-waerk = 'JPY'.

PERFORM round USING wa_sail_ord-b_disc gf_price.

ENDIF.

***Freight1

wa_sail_ord-fright1 = ( lf_fright1 / wa_sail_ord-kwmeng ) *

wa_sail_ord-balqty .

IF wa_sail_ord-waerk = 'JPY'.

PERFORM round USING wa_sail_ord-fright1 gf_price.

ENDIF.

***Freight2

wa_sail_ord-fright2 = ( lf_fright2 / wa_sail_ord-kwmeng ) *

wa_sail_ord-balqty .

IF wa_sail_ord-waerk = 'JPY'.

PERFORM round USING wa_sail_ord-fright2 gf_price.

ENDIF.

ENDIF.

***Total Amount for Balance Quantity

wa_sail_ord-amt_bal_qty = ( wa_sail_ord-u_price *

wa_sail_ord-balqty ) +

wa_sail_ord-s_disc +

wa_sail_ord-b_disc +

wa_sail_ord-fright1 +

wa_sail_ord-fright2 .

IF NOT wa_sail_ord-/rbc2/yv_prodat IS INITIAL.

IF wa_sail_ord-edatu = wa_sail_ord-/rbc2/yv_prodat AND

wa_sail_ord-auart = 'ZSO'.

wa_sail_ord-crd = 'Y'.

ELSE.

wa_sail_ord-crd = 'N'.

ENDIF.

ELSE.

CLEAR : lf_webaz,lf_plifz,lf_loadtg, gs_marc.

READ TABLE gt_marc INTO gs_marc

WITH KEY matnr = wa_sail_ord-matnr

werks = wa_sail_ord-werks

BINARY SEARCH.

IF sy-subrc = 0.

lf_webaz = gs_marc-webaz.

lf_plifz = gs_marc-plifz.

ENDIF.

• loading time

CLEAR gs_tvst.

READ TABLE gt_tvst INTO gs_tvst

WITH KEY vstel = wa_sail_ord-vstel

BINARY SEARCH.

IF sy-subrc = 0.

lf_loadtg = gs_tvst-loadtg.

ENDIF.

PERFORM get_promised_date

USING lf_webaz

lf_plifz

lf_loadtg

wa_sail_ord-audat

wa_sail_ord-/rbc2/yv_prodat.

IF wa_sail_ord-edatu GE wa_sail_ord-/rbc2/yv_prodat AND

wa_sail_ord-auart = 'ZSO'.

wa_sail_ord-crd = 'Y'.

ELSE.

wa_sail_ord-crd = 'N'.

ENDIF.

ENDIF.

CLEAR: wa_vbfa.

READ TABLE gt_vbfa INTO wa_vbfa WITH KEY

vbelv = wa_sail_ord-vbeln

posnv = wa_sail_ord-posnr

vbtyp_n = 'V'

BINARY SEARCH.

IF sy-subrc EQ 0.

wa_sail_ord-po_number = wa_vbfa-vbeln.

wa_sail_ord-posnn = wa_vbfa-posnn.

CLEAR gf_elikz.

PERFORM convertion USING wa_sail_ord-po_number.

PERFORM convertion USING wa_sail_ord-posnn.

SELECT SINGLE elikz

FROM ekpo

INTO gf_elikz

WHERE ebeln = wa_sail_ord-po_number AND

ebelp = wa_sail_ord-posnn.

IF sy-subrc = 0 AND

gf_elikz = 'X'.

wa_sail_ord-elikz = 'Y'.

ENDIF.

ENDIF.

• Customer short name

CLEAR wa_kunnr_name.

READ TABLE gt_kunnr_name INTO wa_kunnr_name

WITH KEY kunnr = wa_sail_ord-kunnr

BINARY SEARCH.

IF sy-subrc = 0.

wa_sail_ord-sortl = wa_kunnr_name-sortl.

ENDIF.

• Sales order stock

CLEAR : wa_sail_ord-kalab,gs_mska.

READ TABLE gt_mska INTO gs_mska

WITH KEY vbeln = wa_sail_ord-vbeln

posnr = wa_sail_ord-posnr

BINARY SEARCH.

IF sy-subrc = 0.

wa_sail_ord-kalab = gs_mska-kalab.

ENDIF.

MODIFY gt_sail_ord FROM wa_sail_ord

TRANSPORTING lfimg fkimg balqty edatu lifsp stat

abgru_text cnfdt

u_price s_disc b_disc fright1 fright2

amt_bal_qty fkdat banfn crd vdatu

po_number posnn kalab elikz sortl.

ENDLOOP.

helpful answers will be rewarded.

[Former Member]

I guess you are executing this FORM.routine while looping over the records contained in global internal table gt_sail_ord, right?

If so, you have to be aware of the fact, that for each records processed (gt_sail_ord), several critical SELECT statements are executed. Sorry, but this just can´t be high-performance: Let´s assume you have 10000 records in gt_sail_ord, this means, that 10000 times 10 SELECT statements have to be executed by the ABAP processor and the database!

Please, make use of internal tables, which keep all the data you are now retrieving from database directly, and read the requested data from these internal tables! Internal table (with good keys) are much quicker than selecting all the data from database

[Former Member]

Another aspect:

the internal tables you are already using do not seem to have a good key, perhaps these tables are even standard tables. Please make use of at least SORTED of even HASHED tables!

[Former Member]

I hope not, but just for the case, that you declared your internal tables with header line...: DON´T DO THAT, THIS IS VERY OUT-DATED!

[Former Member]

Hi Mike,

Sorry, this form is not in any loop. Therefore all the expensive SELECT statements are outside the loop on GT_SAIL_ORD

[Former Member]

OK, sorry, my fault!

Anyway: I found the statement

READ TABLE gt_vbfa INTO wa_vbfa WITH KEY
vbelv = wa_sail_ord-vbeln
posnv = wa_sail_ord-posnr
vbtyp_n = 'J'
BINARY SEARCH.

How about using a sorted or hashed table? Reading accesses againts sorted or hashed tables are much quicker than reading from a standard table without a real table key! I am talking about an performance improvement of factor 10 or higher at least!

Another suggestion: The usage of SQL JOINs instead of FOR ALL ENTRIES IN

Edited by: Mike Schernbeck on Apr 10, 2008 10:58 AM

[Former Member]

Hi,

When i went thru the code i found some minor glitches..let me explain...

select queries...

1.IF NOT gt_sail_ord[] IS INITIAL.

SELECT vbelv posnv vbeln posnn vbtyp_n

FROM vbfa

INTO TABLE gt_vbfa

FOR ALL ENTRIES IN gt_sail_ord

WHERE vbelv = gt_sail_ord-vbeln

AND posnv = gt_sail_ord-posnr.

SORT gt_vbfa BY vbelv posnv vbtyp_n.

then again using gt_vbfa u r getting values from tableS lips and vbrp

IF NOT gt_vbfa[] IS INITIAL.

SELECT vbeln

posnr

lfimg

FROM lips

INTO TABLE gt_lips

FOR ALL ENTRIES IN gt_vbfa

WHERE vbeln = gt_vbfa-vbeln AND

posnr = gt_vbfa-posnn.

SORT gt_lips BY vbeln posnr.

SELECT vbeln

posnr

fkimg

FROM vbrp

INTO TABLE gt_vbrp

FOR ALL ENTRIES IN gt_vbfa

WHERE vbeln = gt_vbfa-vbeln AND

posnr = gt_vbfa-posnn.

SORT gt_vbrp BY vbeln posnr.

so these three can be reduced to 2 select queries....like

first join vbfa & lips

select vbeln

posnr

lfimg

from vbfa as vb inner join lips as lp

on lp_{vbeln = vb}vbeln and

lp_{posnr = vb}posnr

into table gt_lips

for all entries in gt_sail_ord

where vb~vbeln = gt_sail_ord-vbeln AND

vb~posnr = gt_sail_ord-posnn.

similarly join vbrp and vbfa ...as i can see in below code u r doing some processing...Now,,

-

---

ur code----

---

READ TABLE gt_vbfa INTO wa_vbfa WITH KEY

vbelv = wa_sail_ord-vbeln

posnv = wa_sail_ord-posnr

vbtyp_n = 'J'

BINARY SEARCH.

IF sy-subrc = 0.

CLEAR gs_lips.

endif..

READ TABLE gt_lips INTO gs_lips

WITH KEY vbeln = wa_vbfa-vbeln

posnr = wa_vbfa-posnn

BINARY SEARCH.

IF sy-subrc = 0.

wa_sail_ord-lfimg = gs_lips-lfimg.

ENDIF.

ENDIF.

-

---

This can be reduced like....

READ TABLE gt_lips INTO gs_lips

WITH KEY vbeln = wa_vbfa-vbeln

posnr = wa_vbfa-posnn

BINARY SEARCH.

IF sy-subrc = 0.

wa_sail_ord-lfimg = gs_lips-lfimg.

ENDIF. .....sINCE in gt_lips we only have unique records from the select query using join

similarly the below code can also be changed like....

READ TABLE gt_vbrp INTO gs_vbrp

WITH KEY vbeln = wa_vbfa-vbeln

posnr = wa_vbfa-posnn

BINARY SEARCH.

IF sy-subrc = 0.

wa_sail_ord-fkimg = gs_vbrp-fkimg.

ENDIF.

Try this out ..and rearrange ur code, in this u will definitely find a solution....

Reward if found useful.....

Regards,

ABAPer 007.