Performance issue with the Select query

[Former Member]

Hi,

I have an issue with the performance with a seclet query.

In table AFRU - AUFNR is not a key field.

So i had selected the low and high values into s_reuck and used it in Where condition.

Still i have an issue with the Performance.

SELECT SINGLE RUECK

RMZHL

IEDD

AUFNR

STOKZ

STZHL

FROM AFRU INTO table t_AFRU

FOR ALL ENTRIES IN T_ZSCPRT100

WHERE RUECK IN S_RUECK AND

AUFNR = T_ZSCPRT100-AUFNR AND

STOKZ = SPACE AND

STZHL = 0.

I had also cheked by createing an index for AUFNR in the table AFRU...it does not help.

Is there anyway that we can declare Key field while declaring the Internal table....?

ANy suggestions to fix the performance issue is apprecaited!

Regards,

Kittu

[Rui_Dantas]

Hi Kittu,

If you have AUFNR you should:

Using AUFNR, get AUFPL from AFKO

Using AUFPL, get RUECK from AFVC

Using RUEKC, read AFRU

In other words, one select joining AFKO <-> AFVC <-> AFRU should get what you want.

Hope this helps,

Rui dantas

[Former Member]

The advice given by Rui Pedro Dantas is the correct way to go.

But you should also bear in mind that after you create an index, the database statistics should be updated so that the optimizer has the information it needs to pick the correct index.

In addition to saving and activating the index, you also have to create it in the database using 'adjust'.

And try not to create unnecessary indexes on standard SAP tables.

Rob

Edited by: Rob Burbank on Jun 3, 2009 2:59 PM

[Former Member]

Hi,

Very important thing to bear in mind in order to gain porformance in select stmt.

always put all the key fields in where clause, if you dont have the key fields values to put in where clause, do this way:

Just go through the example....

TABLES: S001.

CONSTANTS: SSOUR_INI LIKE S001-SSOUR VALUE IS INITIAL,

VRSIO_INI LIKE S001-VRSIO VALUE IS INITIAL,

SPMON_INI LIKE S001-SPMON VALUE IS INITIAL,

SPTAG_INI LIKE S001-SPTAG VALUE IS INITIAL,

SPWOC_INI LIKE S001-SPWOC VALUE IS INITIAL,

SPBUP_INI LIKE S001-SPBUP VALUE IS INITIAL.

DATA: TA_S001 TYPE STANDARD TABLE OF S001.

DATA: WA_S001 TYPE S001.

SELECT-OPTIONS:

SO_SPTAG FOR S001-SPTAG OBLIGATORY,

SO_KUNNR FOR S001-KUNNR OBLIGATORY,

SO_VKORG FOR S001-VKORG OBLIGATORY,

SO_VTWEG FOR S001-VTWEG OBLIGATORY,

SO_SPART FOR S001-SPART OBLIGATORY,

SO_MATNR FOR S001-MATNR OBLIGATORY.

START-OF-SELECTION.

SELECT * FROM S001

INTO TABLE TA_S001

WHERE SSOUR GE SSOUR_INI "Greater Equal initial value

AND VRSIO GE VRSIO_INI " idem

AND SPMON GE SPMON_INI " idem

AND SPTAG IN SO_SPTAG

AND SPWOC GE SPWOC_INI " idem

AND SPBUP GE SPBUP_INI " idem

AND KUNNR IN SO_KUNNR

AND VKORG IN SO_VKORG

AND VTWEG IN SO_VTWEG

AND SPART IN SO_SPART

AND MATNR IN SO_MATNR.

Thanks,

Krishna..

[Former Member]

Hi,

Thank you for your quick response!

Rui dantas, i have lill confusion...this is my code below :

data : t_zscprt type standard table of ty_zscprt,

wa_zscprt type ty_zscprt.

types : BEGIN OF ty_zscprt100,

aufnr type zscprt100-aufnr,

posnr type zscprt100-posnr,

ezclose type zscprt100-ezclose,

serialnr type zscprt100-serialnr,

lgort type zscprt100-lgort,

END OF ty_zscprt100.

data : t_zscprt100 type standard table of ty_zscprt100,

wa_zscprt100 type ty_zscprt100.

Types: begin of ty_afru,

rueck type CO_RUECK,

rmzhl type CO_RMZHL,

iedd type RU_IEDD,

aufnr type AUFNR,

stokz type CO_STOKZ,

stzhl type CO_STZHL,

end of ty_afru.

data : t_afru type STANDARD TABLE OF ty_afru,

WA_AFRU TYPE TY_AFRU.

SELECT AUFNR

POSNR

EZCLOSE

SERIALNR

LGORT

FROM ZSCPRT100 INTO TABLE T_ZSCPRT100

FOR ALL ENTRIES IN T_ZSCPRT

WHERE AUFNR = T_ZSCPRT-PRTNUM

AND SERIALNR IN S_SERIAL

AND LGORT IN S_LGORT.

IF sy-subrc <> 0.

MESSAGE ID 'Z2' TYPE 'I' NUMBER '41'. "BDCG87

stop."BDCG87

ENDIF.

ENDIF.

SELECT RUECK

RMZHL

IEDD

AUFNR

STOKZ

STZHL

FROM AFRU INTO TABLE T_AFRU

FOR ALL ENTRIES IN T_ZSCPRT100

WHERE RUECK IN S_RUECK AND

AUFNR = T_ZSCPRT100-AUFNR AND

STOKZ = SPACE AND

STZHL = 0.

Using AUFNR, get AUFPL from AFKO

Using AUFPL, get RUECK from AFVC

Using RUEKC, read AFRU

In other words, one select joining AFKO <-> AFVC <-> AFRU should get what you want.

This is my select query, would you want me to write another select query to meet this criteria..

From AUFNR> I will get AUFPL from AFKO> BAsed on AUFPL I will get RUECK, based on RUEKC i need to read AFRU..but i need to select few field from AFRu based on AUFNR....

ANy suggestions wil be appreciated!

Regards

Kittu

[Rui_Dantas]

Hello Kittu,

I don't understand what you didn't understand..

In your code, I don't see where S_RUECK comes from, but I am assuming you don't have the confirmation numbers (RUECK), otherwise you wouldn't have performance problems.

So.. instead of selecting directly from AFRU (using the aufnr you have), you must :

select <the fields you want>
from afko join afvc on afko~aufpl = afvc~aufpl
          join afru on afvc~rueck = afru~rueck
where afko~aufnr = <your aufnr>

(this probably has syntax errors, but I'm sure you can fix that)

Instead of using FOR ALL ENTRIES, you will want also to join your Z table in this query.

You can also check the note 187906 that Siegfried mentioned (point 1). The notes 185530 (SD), 187906 (PP/PM) and 191492 (MM/WM) have some basic hints on how to use R/3 model correctly.

Rui Dantas

[Former Member]

After populating internal table t_zscprt100 use the following code. This appears like what Rui dantas was suggesting.

TABLES: afru.

DATA: BEGIN OF t_zscprt100 OCCURS 0,
        aufnr TYPE afko-aufnr,
      END OF t_zscprt100.

SELECT-OPTIONS: s_rueck FOR afru-rueck.

TYPES: BEGIN OF ty_afru,
         rueck TYPE afru-rueck,
         rmzhl TYPE afru-rmzhl,
         iedd  TYPE afru-iedd ,
         aufnr TYPE afko-aufnr,
         stokz TYPE afru-stokz,
         stzhl TYPE afru-stzhl,
         aufpl TYPE afvc-aufpl,
         aplzl TYPE afvc-aplzl,
       END OF ty_afru.

DATA: t_afru          TYPE TABLE OF ty_afru    ,
      t_zscprt100_tmp LIKE TABLE OF t_zscprt100.

IF NOT t_zscprt100[] IS INITIAL.

  t_zscprt100_tmp[] = t_zscprt100[].

  SORT t_zscprt100_tmp BY aufnr.

  DELETE ADJACENT DUPLICATES FROM t_zscprt100_tmp COMPARING aufnr.

  SELECT c~rueck
         c~rmzhl
         c~iedd
         a~aufnr
         c~stokz
         c~stzhl
         b~aufpl
         b~aplzl
    FROM       afko AS a
    INNER JOIN afvc AS b
    ON  a~aufpl EQ b~aufpl
    INNER JOIN afru AS c
    ON  b~rueck EQ c~rueck
    AND b~rmzhl EQ c~rmzhl
    INTO TABLE t_afru
    FOR ALL ENTRIES IN t_zscprt100_tmp
    WHERE a~aufnr EQ t_zscprt100_tmp-aufnr
    AND   c~rueck IN s_rueck
    AND   c~stokz EQ space
    AND   c~stzhl EQ 0.

ENDIF.

[Former Member]

Please read

SAP Note 187906

Performance in PP & PM:

Tables: AFRU, AUFK, CAUFV, PLAF, AFKO, AFPO, AFVC, RESB

I should help

[Former Member]

Hi,

Sorry for the late response!

Thanks for the suggestions, they were helpful!

Regards,

Kittu