Performance

[Former Member]

Hi

I need to optimize the following code. pls advise me

FORM get_delivery_note_data .

if p_sammg is initial.

• Do nothing

SELECT vbeln werks posnr matnr lfimg erdat ernam meins vgbel lgort

FROM lips INTO TABLE it_lips

*FOR ALL ENTRIES IN it_likp

WHERE werks = p_werks

AND lgort IN s_lgort

AND vbeln IN s_vbeln

AND erdat IN s_erdat

AND ernam IN s_ernam.

Else.

SELECT VBELN INTO TABLE it_VBSS

FROM VBSS

WHERE SAMMG = P_SAMMG.

CHECK SYST-SUBRC = 0.

SELECT vbeln werks posnr matnr lfimg erdat ernam meins vgbel lgort

FROM lips INTO TABLE it_lips

FOR ALL ENTRIES IN it_vbss

WHERE werks = p_werks

AND VBELN = it_vbss-vbeln

AND lgort IN s_lgort

*AND vbeln IN s_vbeln

AND erdat IN s_erdat

AND ernam IN s_ernam.

endif.

IF NOT it_lips[] IS INITIAL.

SELECT vbeln vkorg kunag kunnr lfdat wadat lifnr vstel lfart

FROM likp INTO TABLE it_likp

FOR ALL ENTRIES IN it_lips

WHERE vbeln = it_lips-vbeln

AND vkorg IN s_vkorg

AND wadat IN s_wadat

AND kunag IN s_kunag

AND kunnr IN s_kunwe

AND vstel IN s_vstel

AND lfart IN s_lfart.

SELECT kunnr bukrs

FROM knb1 INTO TABLE it_knb1

FOR ALL ENTRIES IN it_likp

WHERE kunnr = it_likp-kunnr.

SELECT vbeln bstkd FROM vbkd

INTO TABLE it_vbkd

FOR ALL ENTRIES IN it_lips

WHERE vbeln = it_lips-vgbel.

SELECT kunnr name1 name2 stras ort02 pstlz ort01

FROM kna1 INTO TABLE it_kna1

FOR ALL ENTRIES IN it_likp

WHERE kunnr = it_likp-kunnr.

ENDIF.

LOOP AT it_lips.

it_final-werks = it_lips-werks.

it_final-posnr = it_lips-posnr.

it_final-matnr = it_lips-matnr.

it_final-lfimg = it_lips-lfimg.

it_final-erdat = it_lips-erdat.

it_final-ernam = it_lips-ernam.

*it_final-meins = it_lips-meins.

it_final-vgbel = it_lips-vgbel.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'

EXPORTING

input = it_final-vgbel

IMPORTING

output = it_final-vgbel

.

READ TABLE it_likp WITH KEY vbeln = it_lips-vbeln.

IF sy-subrc = 0.

READ TABLE it_vbkd WITH KEY vbeln = it_lips-vgbel.

IF sy-subrc = 0.

it_final-bstkd = it_vbkd-bstkd.

ENDIF.

it_final-vbeln = it_likp-vbeln.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'

EXPORTING

input = it_final-vbeln

IMPORTING

output = it_final-vbeln

.

it_final-vkorg = it_likp-vkorg.

it_final-kunag = it_likp-kunag.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'

EXPORTING

input = it_final-kunag

IMPORTING

output = it_final-kunag.

it_final-kunnr = it_likp-kunnr.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'

EXPORTING

input = it_final-kunnr

IMPORTING

output = it_final-kunnr.

it_final-lfdat = it_likp-lfdat.

it_final-wadat = it_likp-wadat.

it_final-vstel = it_likp-vstel.

*it_final-lfart = it_likp-lfart.

*it_final-lifnr = it_likp-lifnr.

READ TABLE it_knb1 WITH KEY kunnr = it_likp-kunnr.

IF sy-subrc = 0.

it_final-bukrs = it_knb1-bukrs.

ENDIF.

ENDIF.

READ TABLE it_kna1 WITH KEY kunnr = it_likp-kunnr.

IF sy-subrc = 0.

it_final-name1 = it_kna1-name1.

it_final-name2 = it_kna1-name2.

it_final-stras = it_kna1-stras.

it_final-ort02 = it_kna1-ort02.

it_final-pstlz = it_kna1-pstlz.

it_final-ort01 = it_kna1-ort01.

ENDIF.

it_final-mandt = sy-mandt.

it_final-date = sy-datum.

it_final-time = sy-uzeit.

it_final-user = sy-uname.

it_final-file = p_pcfile.

APPEND it_final.

DELETE it_final WHERE vbeln EQ space.

CLEAR : it_lips, it_likp, it_kna1, it_final.

ENDLOOP.

ENDFORM. " GET_DELIVERY_NOTE_DATA

[Former Member]

Hi Kumar,

Use SORT and DELETE ADJASCENT DUPLICATES statement on the Internal tables after each SELECT stmt where FOR ALL ENTRIES is used. Its needed.

And also before every select stmt check whether the comparing / parent table is INITIAL or not...it'll increase the performance...

Reward if helpful.

Regards.

[Former Member]

hi there....

first reorganize your select query. in the where conditions, arrange the conditions in such a manner that the one which is most likely to fail should be first and the least likely condition to fail should be the last.

this is the best optimizing that can be done to this code as the code written by you follows all the optimizing conditions. Good Job!!!

Do reward if the above advice is helpful!!!

[Former Member]

Hi,

1) Use a joing between kna1 and knb1, that will reduce 1 select statement.

2) SELECT vbeln vkorg kunag kunnr lfdat wadat lifnr vstel lfart

FROM likp INTO TABLE it_likp

FOR ALL ENTRIES IN it_lips

WHERE vbeln = it_lips-vbeln

AND vkorg IN s_vkorg

AND wadat IN s_wadat

AND kunag IN s_kunag

AND kunnr IN s_kunwe

AND vstel IN s_vstel

AND lfart IN s_lfart.

In this select statement, conver it_lips-vbeln into a range table so that you can modify the query in this fashion, so that one hit to DB is done:

SELECT vbeln vkorg kunag kunnr lfdat wadat lifnr vstel lfart

FROM likp INTO TABLE it_likp

WHERE vbeln in r_vbeln

AND vkorg IN s_vkorg

AND wadat IN s_wadat

AND kunag IN s_kunag

AND kunnr IN s_kunwe

AND vstel IN s_vstel

AND lfart IN s_lfart.

Pls reward points if useful

Shruthi

Edited by: Shruthi R on May 21, 2008 5:44 AM

[Former Member]

Hi,

You have missed, table initial condition check, beore few "for all entries" statements.

use "DELETE ADJACENT DUPLICATE " after each for all entries statement.

[Former Member]

Hi Kumar k,

FORM get_delivery_note_data .

if p_sammg is initial.


Do nothing 

SELECT vbeln werks posnr matnr lfimg erdat ernam meins vgbel lgort
FROM lips INTO TABLE it_lips
*FOR ALL ENTRIES IN it_likp
WHERE werks = p_werks
AND lgort IN s_lgort
AND vbeln IN s_vbeln
AND erdat IN s_erdat
AND ernam IN s_ernam.

Else.
SELECT VBELN INTO TABLE it_VBSS
FROM VBSS
WHERE SAMMG = P_SAMMG.
CHECK SYST-SUBRC = 0.

SELECT vbeln werks posnr matnr lfimg erdat ernam meins vgbel lgort
FROM lips INTO TABLE it_lips
FOR ALL ENTRIES IN it_vbss
WHERE werks = p_werks
AND VBELN = it_vbss-vbeln
AND lgort IN s_lgort
*AND vbeln IN s_vbeln
AND erdat IN s_erdat
AND ernam IN s_ernam.

endif.

IF NOT it_lips[] IS INITIAL.

SELECT vbeln vkorg kunag kunnr lfdat wadat lifnr vstel lfart
FROM likp INTO TABLE it_likp
FOR ALL ENTRIES IN it_lips
WHERE vbeln = it_lips-vbeln
AND vkorg IN s_vkorg
AND wadat IN s_wadat
AND kunag IN s_kunag
AND kunnr IN s_kunwe
AND vstel IN s_vstel
AND lfart IN s_lfart.

SELECT vbeln bstkd FROM vbkd
INTO TABLE it_vbkd
FOR ALL ENTRIES IN it_lips
WHERE vbeln = it_lips-vgbel.

ENDIF.

Chages required here...

U must check for table is initial or not b4 using FOR ALL ENTRIES...

which u didn't...

The worse effect of it is if the it_LIKP[] is initial then it will fetch the data from table kna1 and knb1....

See the below code ...

IF NOT it_LIKP[] IS INITIAL.

SELECT kunnr name1 name2 stras ort02 pstlz ort01
FROM kna1 INTO TABLE it_kna1
FOR ALL ENTRIES IN it_likp
WHERE kunnr = it_likp-kunnr.

SELECT kunnr bukrs
FROM knb1 INTO TABLE it_knb1
FOR ALL ENTRIES IN it_likp
WHERE kunnr = it_likp-kunnr.

ENDIF.

LOOP AT it_lips.

it_final-werks = it_lips-werks.
it_final-posnr = it_lips-posnr.
it_final-matnr = it_lips-matnr.
it_final-lfimg = it_lips-lfimg.
it_final-erdat = it_lips-erdat.
it_final-ernam = it_lips-ernam.
*it_final-meins = it_lips-meins.
it_final-vgbel = it_lips-vgbel.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'
EXPORTING
input = it_final-vgbel
IMPORTING
output = it_final-vgbel
.
READ TABLE it_likp WITH KEY vbeln = it_lips-vbeln.
IF sy-subrc = 0.
READ TABLE it_vbkd WITH KEY vbeln = it_lips-vgbel.
IF sy-subrc = 0.
it_final-bstkd = it_vbkd-bstkd.
ENDIF.
it_final-vbeln = it_likp-vbeln.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'
EXPORTING
input = it_final-vbeln
IMPORTING
output = it_final-vbeln
.
it_final-vkorg = it_likp-vkorg.
it_final-kunag = it_likp-kunag.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'
EXPORTING
input = it_final-kunag
IMPORTING
output = it_final-kunag.

it_final-kunnr = it_likp-kunnr.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'
EXPORTING
input = it_final-kunnr
IMPORTING
output = it_final-kunnr.

it_final-lfdat = it_likp-lfdat.
it_final-wadat = it_likp-wadat.
it_final-vstel = it_likp-vstel.
*it_final-lfart = it_likp-lfart.
*it_final-lifnr = it_likp-lifnr.

READ TABLE it_knb1 WITH KEY kunnr = it_likp-kunnr.
IF sy-subrc = 0.
it_final-bukrs = it_knb1-bukrs.
ENDIF.
ENDIF.



READ TABLE it_kna1 WITH KEY kunnr = it_likp-kunnr.
IF sy-subrc = 0.
it_final-name1 = it_kna1-name1.
it_final-name2 = it_kna1-name2.
it_final-stras = it_kna1-stras.
it_final-ort02 = it_kna1-ort02.
it_final-pstlz = it_kna1-pstlz.
it_final-ort01 = it_kna1-ort01.
ENDIF.
it_final-mandt = sy-mandt.
it_final-date = sy-datum.
it_final-time = sy-uzeit.
it_final-user = sy-uname.
it_final-file = p_pcfile.

APPEND it_final.
DELETE it_final WHERE vbeln EQ space.

CLEAR : it_lips, it_likp, it_kna1, it_final.

ENDLOOP.

ENDFORM. " GET_DELIVERY_NOTE_DATA

Hope it will solve your problem...

Reward points if useful...

Thanks & Regards

ilesh 24x7

[Former Member]

Apart from the above suggestions ,

you can also use field symbols for modifying entries in the internal table.

do not use sort inside the loop.It will reduce the performance of the code.

Also in the select queries where condition, arrange the fields in the order in which teh appear in the table/structure.

In case ur using for all entries give the field of the internal table ur referring to first and then arrange the remaining fields according to the able/structure.

for eg

select ebeln ebelp aedat

from ekpo

into correspondig fields of table itab2

for all entries in it_itab1

where ebeln = itab-ebeln

and aedat in so_aedat

and pspnr in so_pspnr.

Please do reward points if you find this useful