Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

replacing innerjoin with for all entries

Former Member
0 Kudos

hi,

i have a requirement that i have to improve the performance of report . this the code below.


SELECT  A~VBELN A~BSTNK A~AUDAT A~KUNNR B~MATNR  B~WERKS B~KWMENG
       FROM VBAK AS A INNER JOIN VBAP  AS B ON A~VBELN = B~VBELN
       INTO CORRESPONDING FIELDS OF TABLE ITEKKo WHERE  
B~MATKL IN S_MATKL AND A~AUDAT IN S_BEDAT AND 
A~VKORG IN S_VKORG AND A~VTWEG IN S_VTWEG 
   AND B~WERKS IN S_WERKS .

LOOP AT ITEKKO.
  SELECT SINGLE MVGR1 MVGR5 FROM MVKE INTO (ITEKKO-MATKL, ITEKKO-MVGR5)  WHERE 
MATNR = ITEKKO-MATNR.
  SELECT SINGLE INCO2 FROM VBKD INTO ITEKKO-NAME1 WHERe VBELN = ITEKKO-VBELN.
  SELECT SINGLE LAND1 FROM KNA1 INTO CCODE WHERE 
            KUNNR = ITEKKO-KUNNR.
  SELECT SINGLE LANDX50 FROM T005T INTO ITEKKO-CNAME WHERE 
            LAND1 = CCODE   AND SPRAS = 'E'.
  MODIFY ITEKKO.
ENDLOOP.


SELECT C~BLDAT D~VBELN D~POSNR D~VGBEL
           D~MATKL D~MFRGR D~LFIMG D~MEINS D~MATNR
       FROM LIKP AS C INNER JOIN LIPS AS D ON C~VBELN = D~VBELN
       INNER JOIN VBAK AS A ON D~VGBEL = A~VBELN
       INTO CORRESPONDING FIELDS OF TABLE IT_LIPS
       WHERE A~AUDAT IN S_BEDAT
       ORDER BY D~VGBEL D~MFRGR.

LOOP AT IT_LIPS.
  SELECT SINGLE MVGR5 FROM MVKE INTO IT_LIPS-MVGR5 WHERE
                                  MATNR = IT_LIPS-MATNR.
  MODIFY IT_LIPS.
ENDLOOP.

it is taking very long time when the loops r executing.

can anybody please help.

1 ACCEPTED SOLUTION

nabheetscn
Active Contributor
0 Kudos

1. Remove corresponding fields and create a type.

2. Dont use select inside loop. Use for all entries instead of it and then read inside the loop.

select (fields) from vbak into table lt_vbak where A~AUDAT IN S_BEDAT AND

AVKORG IN S_VKORG AND AVTWEG IN S_VTWEG .

if sy-subrc eq 0.

select (fields) from vbap for all entries in lt_vbak where vbeln eq lt_vbak-vbeln and matkl in s_matkl and werks in s_werks

endif.

endif.

put all select which are inside the loop here.

then statrt your loop with lt_vbap.

and append it to EKKO table.

Similarly for others.

Nabheet

15 REPLIES 15

former_member209696
Participant
0 Kudos

Hai Mahesh,

To improve the performance of the pgm try to followings....

1. Don't use "CORRESPONDING FIELDS TO' instead use 'TO' only..(declare internal table with fields in order)

2. Don't use SELECT query inside LOOP.... instead of that use FOR ALL ENTRIES"

0 Kudos

hi,

can you please suggest me some sample code for all entries.

i have tried all conditions are not satisfying based on selection screen

fields which r all not key fields on selection screen.

0 Kudos

LOOP AT ITEKKO.

SELECT SINGLE MVGR1

MVGR5 FROM MVKE INTO <itab> WHERE

MATNR = ITEKKO-MATNR.

ENDLOOP.

instead of this code u can us

SELECT MVGR1

MVGR5 FROM MVKE INTO <itab> FOR ALL ENTRIES IN ITEKKO

WHERE MATNR= ITEKKO-MATNR.

_______________________________________________________________

In ur select query u are fetching one by one by using LOOP.

In FOR ALL ENTRIES it compare all the entries in first internal table and check the condition and it will fetch all the entries into second table in one step.

0 Kudos

hi,

thx.

how can i remove those joins in the script based on selection screen fields.

0 Kudos

SELECT A~VBELN

A~BSTNK

A~AUDAT

A~KUNNR

B~MATNR

B~WERKS

B~KWMENG

FROM VBAK AS A INNER JOIN VBAP AS B

ON AVBELN = BVBELN

INTO CORRESPONDING FIELDS OF TABLE ITEKKO

WHERE B~MATKL IN S_MATKL

AND A~AUDAT IN S_BEDAT

AND A~VKORG IN S_VKORG

AND A~VTWEG IN S_VTWEG

AND B~WERKS IN S_WERKS.

For the above select query u r fetching data from VBAK and VBAP.

VBAK : Sales document header table

VBAP : Sales document item table

VBELN is the common field in between them.

U want to check some other conditions also like:

MATKL IN S_MATKL (field in VBAP table)

AUDAT IN S_BEDAT (field in VBAK table)

VKORG IN S_VKORG(field in VBAK table)

VTWEG IN S_VTWEG (field in VBAK table)

WERKS IN S_WER(field in VBAP table)

If u want to change this code to FOR ALL ENTRIES plz try below code

SELECT VBELN

BSTNK

AUDAT

KUNNR

FROM VBAK

INTO TABLE <itab1>

where AUDAT IN S_BEDAT

and VKORG IN S_VKORG

and VTWEG IN S_VTWEG.

If <itab1> is not initial.

SELECT VBELN (fetch common field in both query)

MATNR

WERKS

KWMENG

FROM VBAP

INTO TABLE <itab2>

FOR ALL ENTRIES IN <itab1>

WHERE vbeln = <itab1>-vbeln

AND MATKL IN S_MATKL

AND WERKS IN S_WER.

endif.

Hope this will give u a clear idea

nabheetscn
Active Contributor
0 Kudos

1. Remove corresponding fields and create a type.

2. Dont use select inside loop. Use for all entries instead of it and then read inside the loop.

select (fields) from vbak into table lt_vbak where A~AUDAT IN S_BEDAT AND

AVKORG IN S_VKORG AND AVTWEG IN S_VTWEG .

if sy-subrc eq 0.

select (fields) from vbap for all entries in lt_vbak where vbeln eq lt_vbak-vbeln and matkl in s_matkl and werks in s_werks

endif.

endif.

put all select which are inside the loop here.

then statrt your loop with lt_vbap.

and append it to EKKO table.

Similarly for others.

Nabheet

0 Kudos

hi riyas & madan,

i have removed the select in loop and my code is,


DATA: BEGIN OF ITMVKE OCCURS 0,
        MVGR1 TYPE MVKE-MVGR1,
        MVGR5 TYPE MVKE-MVGR5,
  END OF ITMVKE.

  SELECT MVGR1 MVGR5 FROM MVKE INTO ITMVKE FOR ALL ENTRIES IN
                               ITEKKO WHERE MATNR = ITEKKO-MATNR.
    IF SY-SUBRC EQ 0.
      LOOP AT ITEKKO.
        READ TABLE ITMVKE INDEX SY-TABIX .
        ITEKKO-MATKL = ITMVKE-MVGR1.
        ITEKKO-MVGR5 = ITMVKE-MVGR5.
        MODIFY ITEKKO. 
      ENDLOOP.
    ENDIF.
  ENDSELECT.

problem is i have ten diff codes(say b c d e f t n s etc..), when the select query

executes it is retrieving first code and is modifying the table itekko(550 entries) with same code,

it is not retrieving the other codes . and there will be duplicate codes also\

how can i resolve this issue.

pls help me.

0 Kudos

change SELECT MVGR1 MVGR5 FROM MVKE INTO ITMVKE FOR ALL ENTRIES IN

ITEKKO WHERE MATNR = ITEKKO-MATNR.

this to

SELECT MVGR1 MVGR5 FROM MVKE INTO *TABLE* ITMVKE FOR ALL ENTRIES IN
                               ITEKKO WHERE MATNR = ITEKKO-MATNR.

0 Kudos

Change as below

DATA: BEGIN OF ITMVKE OCCURS 0,
        *MATNR  TYPE MATNR_D,*        
        MVGR1 TYPE MVKE-MVGR1,
        MVGR5 TYPE MVKE-MVGR5,
  END OF ITMVKE.
 
  SELECT MATNR MVGR1 MVGR5 FROM MVKE INTO *TABLE  ITMVKE* FOR ALL ENTRIES IN
                               ITEKKO WHERE MATNR = ITEKKO-MATNR.
    IF SY-SUBRC EQ 0.
      LOOP AT ITEKKO.
        *READ TABLE ITMVKE WITH KEY MATNR = ITEKKO-MATNR.*
      *IF SY-SUBRC EQ 0.*        ITEKKO-MATKL = ITMVKE-MVGR1.
        ITEKKO-MVGR5 = ITMVKE-MVGR5.
     *ENDIF.*        MODIFY ITEKKO. 
      ENDLOOP.
    ENDIF.

Edited by: nabheetmadan09 on Dec 1, 2011 2:27 PM

0 Kudos

actually what is ur correct requirement.................

?????

instead of READ TABLE ITMVKE INDEX SY-TABIX .

use

READ TABLE ITMVKE WITH KEY <condition> "I think u r comparing material name

Former Member
0 Kudos

hi ,

i want to replace join with for all entries in below code.


SELECT C~BLDAT D~VBELN D~POSNR D~VGBEL D~MATKL D~MFRGR D~LFIMG D~MEINS D~MATNR
       FROM LIKP AS C INNER JOIN LIPS AS D ON C~VBELN = D~VBELN
       INNER JOIN VBAK AS A ON D~VGBEL = A~VBELN
       INTO CORRESPONDING FIELDS OF TABLE IT_LIPS
       WHERE A~AUDAT IN S_BEDAT
       ORDER BY D~VGBEL D~MFRGR.

can u pls suggest me how to remove order by.

sample code pls.

0 Kudos
SELECT VGBEL BLDAT INTO TABLE <ITAB1> FROM   LIKP WHERE <CONDITIONS>.
IF <ITAB1> IS NOT INITIAL.
SELECT VBELN  POSNR VGBEL MATKL  MFRGR LFIMG MEINS MATNR INTO TABLE <ITAB2> FROM LIPS WHERE <CONDITION>.
ENDIF.

iNSTEAD OF ORDER BY

U CAN USE

SORT BY <FIELD1> <FIELD2>

0 Kudos

Hi,

Check this coding,

TYPES:BEGIN OF Y_TAB,

BLDAT TYPE LIKP-BLDAT,

VBELN TYPE LIPS-VBELN,

POSNR TYPE LIPS-POSNR,

VGBEL TYPE LIPS-VGBEL,

MATKL TYPE LIPS-MATKL,

MFRGR TYPE LIPS-MFRGR,

LFIMG TYPE LIPS-LFIMG,

MEINS TYPE LIPS-MEINS,

MATNR TYPE LIPS-MATNR,

END OF LS_TAB.

TYPES : BEGIN OF Y_VBAK,

VBELN TYPE VBAK-VBELN,

END OF Y_VBAK.

TYPES: BEGIN OF Y_LIPS,

VBELN TYPE LIPS-VBELN,

POSNR TYPE LIPS-POSNR,

VGBEL TYPE LIPS-VGBEL,

MATKL TYPE LIPS-MATKL,

MFRGR TYPE LIPS-MFRGR,

LFIMG TYPE LIPS-LFIMG,

MEINS TYPE LIPS-MEINS,

MATNR TYPE LIPS-MATNR,

END OF Y_LIPS.

TYPES : BEGIN OF Y_LIKP,

BLDAT TYPE LIKP-BLDAT,

VBELN TYPE LIKP-VBELN,

END OF Y_LIKP.

DATA IT_ITAB TYPE TABLE OF LS_TAB.

DATA WA_ITAB TYPE LS_TAB.

DATA IT_LIPS TYPE TABLE OF Y_LIPS.

DATA WA_LIPS TYPE Y_LIPS.

DATA IT_LIKP TYPE TABLE OF Y_LIKP.

DATA IT_VBAK TYPE TABLE OF Y_VBAK.

DATA WA_LIKP TYPE Y_LIKP.

SELECT VBELN FROM VBAK INTO TABLE IT_VBAK WHERE AUDAT IN S_BEDAT.

SELECT VBELN POSNR VGBEL MATKL MFRGR LFIMG MEINS MATNR FROM

LIPS INTO TABLE IT_LIPS FOR ALL ENTRIES IN IT_VBAK

WHERE VGBEL = IT_VBAK-VBELN.

SELECT BLDAT VBELN FROM LIKP INTO TABLE IT_LIKP FOR ALL ENTRIES IN IT_LIPS WHERE VBELN = IT_LIPS-VBELN.

LOOP AT IT_LIKP INTO WA_LIKP.

READ TABLE IT_LIPS INTO WA_LIPS WITH KEY VBELN = WA_LIKP-VBELN.

IF SY-SUBRC EQ 0.

MOVE-CORRESPONDING WA_LIPS TO WA_ITAB.

MOVE WA_LIKP-BLDAT TO WA_ITAB-BLDAT.

APPEND WA_ITAB TO IT_ITAB.

CLEAR WA_ITAB.

ENDIF.

ENDLOOP.

SORT IT_ITAB BY VGBEL MFRGR.

I Hope it will help.

Former Member
0 Kudos

What made you think that FAEI (for all entries in) would be better than a join? Depending upon the underlying database system, FAEI can be a very poor choice...particularly in SAP on DB2 systems. In my experience, FAEI can sometimes be useful, but it is not a given that it will improve performance.

Why don't you get and read SAP Note 185530 for how to report from SD? Then, use the appropriate propriety index table (see the Note) for what you need to retrieve, then get the content of VBAK and VBAP. IMHO, nobody should attempt to report from SD without having first read this Note.

Former Member
0 Kudos

thanx to all for helping out.