code optimisation

[Former Member]

Experts

look at the below code , these statment is taking long

time to execute, problem is requirement is such that

i cannot go for primary key,can anybudy optimise

my code without losing any valuable data

SELECT VBAK_{VKORG VBAK}VBTYP VBAK_{AUART VBAK}VTWEG VBAK~SPART

VBAK_{KUNNR VBAK}KVGR1 VBAK_{KVGR3 VBAP}WERKS VBAP~MATNR

VBAP_{MVGR1 VBAP}MVGR2 VBAP_{MVGR3 VBAP}MVGR3 VBAP~MVGR4

VBAP_{MVGR5 VBEP}VBELN VBEP_{POSNR VBEP}ETENR VBEP~MBDAT

VBAP_{KDMAT VBEP}EDATU

INTO CORRESPONDING FIELDS OF

TABLE INT_OPEN_SCH_LINES

FROM VBAK

INNER JOIN VBAP ON VBAK_{VBELN = VBAP}VBELN

INNER JOIN VBEP ON VBEP_{POSNR = VBAP}POSNR AND

VBEP_{VBELN = VBAK}VBELN AND

VBEP_{VBELN = VBAP}VBELN

WHERE VBAP~WERKS IN S_WERKS AND

VBAP~MATNR IN S_MATNR AND

VBAP~MATKL IN S_MATKL AND

VBAP~ABGRU IN S_ABGRU AND

VBAK~KUNNR IN S_KUNNR AND

VBEP~MBDAT = D0 AND

VBAK~VBTYP IN S_VBTYP AND

VBAK~AUART IN S_AUART AND

VBAK~KVGR3 IN S_KVGR3.

[Former Member]

Hi,

In se30 transaction you can look for

Tip&TRicks button on application toolbar

apart from below conventions

Follow below steps

1) Remove corresponding from select satement

2) Remove * from select

3) Select field in sequence as defined in database

4) Avoid unnecessary selects

i.e check for internal table not initial

5) Use all entries and sort table by key fields

6) Remove selects ferom loop and use binary search

7) Try to use secondary index when you don't have

full key.

😎 Modify internal table use transporting option

9) Avoid nested loop . Use read table and loop at itab

from sy-tabix statement.

10) free intrenal table memory wnen table is not

required for further processing.

11)

Follow below logic.

FORM SUB_SELECTION_AUFKTAB.

if not it_plant[] is initial.

it_plant1[] = it_plant[].

sort it_plant1 by werks.

delete adjacent duplicates from it_plant1 comparing werks

SELECT AUFNR KTEXT USER4 OBJNR INTO CORRESPONDING FIELDS OF TABLE I_AUFKTAB

FROM AUFK

FOR ALL ENTRIES IN it_plant1

WHERE AUFNR IN S_AUFNR AND

KTEXT IN S_KTEXT AND

• WERKS IN S_WERKS AND

AUART IN S_AUART AND

USER4 IN S_USER4 AND

werks eq it_plant1-werks.

free it_plant1.

Endif.

ENDFORM. "SUB_SELECTION_AUFKTAB

Regards

Amole

Regards

Amole

[Former Member]

Table VAPMA is a secondary index table for sales order items based on material. Try that.

Rob

[Former Member]

Like this:

SELECT  VBAK~VKORG VBAK~VBTYP VBAK~AUART VBAK~VTWEG VBAK~SPART
        VBAK~KUNNR VBAK~KVGR1 VBAK~KVGR3 VBAP~WERKS VBAP~MATNR
        VBAP~MVGR1 VBAP~MVGR2 VBAP~MVGR3 VBAP~MVGR3 VBAP~MVGR4
        VBAP~MVGR5 VBEP~VBELN VBEP~POSNR VBEP~ETENR VBEP~MBDAT
        VBAP~KDMAT VBEP~EDATU

  INTO CORRESPONDING FIELDS OF
        TABLE INT_OPEN_SCH_LINES
        
  FROM  vapma
    INNER JOIN vbap  ON vapma~vbeln = vbap~vbeln AND
                        vapma~posnr = vbap~posnr
    INNER JOIN vbak  ON vbap~vbeln  = vbak~vbeln
    INNER JOIN vbep  ON vbep~posnr  = vbap~posnr AND
                        vbep~vbeln = vbak~vbeln AND
                        vbep~vbeln = vbap~vbeln


  WHERE vapma~matnr IN s_matnr AND
        vbap~werks IN s_werks AND
        VBAP~MATKL IN S_MATKL AND
        VBAP~ABGRU IN S_ABGRU AND
        VBAK~KUNNR IN S_KUNNR AND
        VBEP~MBDAT = D0 AND
        VBAK~VBTYP IN S_VBTYP AND
        VBAK~AUART IN S_AUART AND
        VBAK~KVGR3 IN S_KVGR3.

Rob

Message was edited by: Rob Burbank

The WHERE claus was still incorrect. I changed it to select using MATNR from VAPMA (which was the whole point to start with).

Message was edited by: Rob Burbank

The JOINs still had problems. I've re-written them and actually tested it this time. It seems to work and runs reasonably quickly.

Message was edited by: Rob Burbank

[Former Member]

  from  vbap        
    inner join VBAK  on vbap~vbeln = vbak~vbeln
    inner join vapma on vbap~matnr = vapma~vbeln
    INNER JOIN VBEP  ON VBEP~POSNR = VBAP~POSNR AND
               VBEP~VBELN = VBAK~VBELN AND
               VBEP~VBELN = VBAP~VBELN

I have a couple of issues with this

1. You are joing a material number to a order number

2. i think the last line is unnecesary

3. i am not of the value of using VAPMA

  from  vbap        
    inner join VBAK  on vbak~vbeln = vbap~vbeln
    INNER JOIN VBEP  ON 
               VBEP~VBELN = VBAP~VBELN AND
               VBEP~POSNR = VBAP~POSNR.

this seems to work on my system, anyway

[Former Member]

You're right on the first two. I've changed the code. (This is what happens when you don't test.) But what's the problem with VAPMA?

Rob

[Former Member]

i just dont see why you need it...

[Former Member]

Because the original select isn't using any key fields in the WHERE clause. Adding VAPMA allows the use of its index on MATNR.

Perhaps the original poster would try it and let us know if it helps or not.

Rob

[Former Member]

And of course, check that S_MATNR is not empty.

Rob

[Former Member]

OK - I tried it. The modified code runs over 4000 times more quickly for a single material and about 9 times more quickly for a range of 100 materials.

They are about even at 1000 materials. (Index uusage becomes less important when selecting large volumes of data.)

Rob

Message was edited by: Rob Burbank