Performance on Select's LIKP, LIPS, VBUK, VBUP..ect..

[Former Member]

Hello Abap guru's...

I have a performance issue on this select, Please give me good input's!

SELECT VBELN LFART VKORG LFDAT INCO2 ROUTE FAKSK KUNNR KUNAG

WAERK BLDAT VSART ZZINS_FACE ZZDELY_VAL ZZLSCH FROM LIKP

INTO TABLE ISO_LIKP

WHERE VBELN IN S_VBELN AND

VKORG IN S_VKORG AND

LFART IN S_LFART AND "('ZLF','ZNL','ZRT') AND

VSART IN S_VSART AND

ROUTE IN S_ROUTE AND

KUNNR IN S_KUNNR AND

KUNAG IN S_KUNAG.

SELECT VBELN POSNR ERNAM ERZET ERDAT MATNR MATKL WERKS LGORT CHARG

LFIMG VBELV POSNV VRKME ARKTX VGBEL VGPOS VTWEG SPART SOBKZ FROM LIPS

INTO TABLE IST_LIPS

FOR ALL ENTRIES IN ISO_VBUP

WHERE VBELN = ISO_VBUP-VBELN AND

POSNR = ISO_VBUP-POSNR AND

ERNAM IN S_ERNAM AND

ERDAT IN S_ERDAT AND

MATNR IN S_MATNR AND

WERKS IN S_WERKS AND

CHARG IN S_CHARG AND

VGBEL IN S_VGBEL AND

VGPOS IN S_VGPOS AND

VTWEG IN S_VTWEG AND

SPART IN S_SPART.

SELECT VBELN LFSTK FKSTK FROM VBUK

INTO TABLE ISO_VBUK

FOR ALL ENTRIES IN ISO_LIKP

WHERE VBELN = ISO_LIKP-VBELN AND " FKSTK IN S_FKSTK

WBSTK <> 'C'.

SELECT VBELN POSNR LFSTA WBSTA FKSTA KOSTA FROM VBUP

INTO TABLE ISO_VBUP

FOR ALL ENTRIES IN ISO_VBUK

WHERE VBELN = ISO_VBUK-VBELN AND " POSNR = IST_LIPS-POSNR AND

LFSTA IN S_LFSTA AND FKSTA IN S_FKSTA AND

WBSTA <> 'C'.

SELECT OBJEK ATINN ATWRT ATFLV FROM AUSP

INTO TABLE IST_AUSP

FOR ALL ENTRIES IN ISO_MCH1

WHERE OBJEK = ISO_MCH1-CUOBJ_BM AND

ATINN IN (L_A_NET_WT,L_A_PACK_WT,L_A_PRODUCT_FORM,L_A_SR_GRADE,L_A_THICK_MM,

L_A_WIDTH_MM,L_A_LENGTH_MM,L_A_COATING_GSM,L_A_LOCATION).

<removed_by_moderator>

Regards,

Raju

Edited by: Julius Bussche on Sep 1, 2008 5:50 PM

[Former Member]

Hi Raj,

first, check wheather all the fields in the where condition are in sequence of the source datatable.

Second and most important think, before every SELECT statement with FOR ALL ENTRIES, check wheather the comparing internal table is empty or not.

Because if you put an initial internal table in FOR ALL ENTRIES , it will fetch all data from the data table.

(REF: http://help.sap.com/saphelp_nw04/helpdata/en/fc/eb3a1f358411d1829f0000e829fbfe/content.htm )

Regards,

Anirban

[former_member387317]

Hi

Check whether the table is empty or not first then only use FOR ALL ENTRIES...

In worst case if table is empty then it will fetch whole table data and can make worst performance and data problems so be careful about checking whether table is empty or now while u r using FOR ALL ENTRIES...

Check out below code...

SELECT VBELN LFART VKORG LFDAT INCO2 ROUTE FAKSK KUNNR KUNAG
WAERK BLDAT VSART ZZINS_FACE ZZDELY_VAL ZZLSCH FROM LIKP
       INTO TABLE ISO_LIKP
       WHERE VBELN IN S_VBELN AND
       VKORG IN S_VKORG AND
       LFART IN S_LFART AND "('ZLF','ZNL','ZRT') AND
       VSART IN S_VSART AND
       ROUTE IN S_ROUTE AND
       KUNNR IN S_KUNNR AND
       KUNAG IN S_KUNAG.

IF ISO_VBUP[] IS NOT INITIAL.

  SELECT VBELN POSNR ERNAM ERZET ERDAT MATNR MATKL WERKS LGORT CHARG
  LFIMG VBELV POSNV VRKME ARKTX VGBEL VGPOS VTWEG SPART SOBKZ FROM LIPS
        INTO TABLE IST_LIPS
        FOR ALL ENTRIES IN ISO_VBUP
        WHERE VBELN = ISO_VBUP-VBELN AND
        POSNR = ISO_VBUP-POSNR AND
        ERNAM IN S_ERNAM AND
        ERDAT IN S_ERDAT AND
        MATNR IN S_MATNR AND
        WERKS IN S_WERKS AND
        CHARG IN S_CHARG AND
        VGBEL IN S_VGBEL AND
        VGPOS IN S_VGPOS AND
        VTWEG IN S_VTWEG AND
        SPART IN S_SPART.

ENDIF.

IF ISO_LIKP[] IS NOT INITIAL.

  SELECT VBELN LFSTK FKSTK FROM VBUK
        INTO TABLE ISO_VBUK
        FOR ALL ENTRIES IN ISO_LIKP
        WHERE VBELN = ISO_LIKP-VBELN AND " FKSTK IN S_FKSTK
        WBSTK 'C'.
ENDIF.

IF ISO_VBUK[] IS NOT INITIAL.
  SELECT VBELN POSNR LFSTA WBSTA FKSTA KOSTA FROM VBUP
        INTO TABLE ISO_VBUP
        FOR ALL ENTRIES IN ISO_VBUK
        WHERE VBELN = ISO_VBUK-VBELN AND " POSNR = IST_LIPS-POSNR AND
        LFSTA IN S_LFSTA AND FKSTA IN S_FKSTA AND
        WBSTA 'C'.
ENDIF.

IF ISO_MCH1[] IS NOT INITIAL.
  SELECT OBJEK ATINN ATWRT ATFLV FROM AUSP
       INTO TABLE IST_AUSP
       FOR ALL ENTRIES IN ISO_MCH1
       WHERE OBJEK = ISO_MCH1-CUOBJ_BM AND
       ATINN IN (L_A_NET_WT,L_A_PACK_WT,L_A_PRODUCT_FORM,L_A_SR_GRADE,L_A_THICK_MM,
       L_A_WIDTH_MM,L_A_LENGTH_MM,L_A_COATING_GSM,L_A_LOCATION).

ENDIF.

Hope it will solve your problem..

Thanks & Regards

ilesh 24x7

[alison_lloyd]

Also check you are not using duplicates for for all entries access: in addition to being not empty the for all entries table should be sorted in the order of the fields you are using to select by and should contain no duplicate entries based on these fields - if neccessary define auxiliary tables so you can delete adjacent duplicates

[Former Member]

Hi Raj,

Please take the below points to avoid performance issues,

1) Check if the table is sorted before using the for all entries

2) .Check if the internal table is initial before using the for all entries.

3)select the fields in the order they are in the database to minimize database time.