increase speed up program

[former_member210823]

hello

it is my report ,the last query is very slowly, I used index in AUFK, for objnr field and AFVC for Objnr field, but  in last query that use is very slowwllyy.

REPORT  ZSK_TEST.

TYPE-POOLS: slis,kkblo,

             vrm.

TABLES : AFKO,AUFK.

*parameters

SELECTION-SCREEN BEGIN OF BLOCK ORDER  WITH FRAME TITLE text-t02.

  SELECTION-SCREEN BEGIN OF LINE.

  SELECTION-SCREEN COMMENT 1(20) text-113.

  SELECT-OPTIONS:MATERIAL FOR AFKO-PLNBEZ ."obligatory .

  SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN END OF BLOCK ORDER.

types:begin of ty_all_ord,

   aufnr type CAUFV-aufnr,

end of ty_all_ord.

types:begin of ty_all_ord_pcnf,

   aufnr type CAUFV-aufnr,

end of ty_all_ord_pcnf.

types:begin of ty_all_ord_link,

   aufpl type afko-aufpl,

   APLZL type afvc-APLZL,

end of ty_all_ord_link.

types:begin of ty_all_obj,

   vornr type afvc-vornr,

end of ty_all_obj.

types:begin of ty_all_op,

   "aufnr type CAUFV-aufnr,

   vornr type afvc-vornr,

   "objnr type afvc-objnr,

end of ty_all_op.

types:begin of ty_all_ord_grnk,

   aufnr type CAUFV-aufnr,

end of ty_all_ord_grnk.

data: it_all_ord type standard table of  ty_all_ord  .

data: it_all_ord_pcnf type standard table of  ty_all_ord_pcnf  .

data: it_all_ord_grnk type standard table of  ty_all_ord_grnk  .

data: it_all_ord_link type standard table of  ty_all_ord_link  .

data: it_all_obj type standard table of  ty_all_obj  .

data: it_all_op type standard table of  ty_all_op .

data :wa_last_op_cnf type afvc-vornr.

data :wa_last_op_pcnf  type afvc-vornr.

data :wa_last_st type jest-stat.

FIELD-SYMBOLS: <lfs_all_ord_grnk> LIKE LINE OF  it_all_ord_grnk.

FIELD-SYMBOLS: <lfs_all_ord_link> LIKE LINE OF  it_all_ord_link.

"get all orders for materials

select afpo~aufnr

     from afpo

     into table it_all_ord

     where afpo~matnr in material.

if it_all_ord is not initial.

"get PCNF orders

select aufk~aufnr

     from aufk join jest on aufk~objnr = jest~objnr

     into table it_all_ord_pcnf

     for all entries in it_all_ord

     where  inact eq ' ' and stat eq 'I0010' and

           aufk~aufnr = it_all_ord-aufnr.

if it_all_ord is not initial.

"get GRNK orders

   select aufk~aufnr

     from aufk join jest on aufk~objnr = jest~objnr

     into table it_all_ord_grnk

     for all entries in it_all_ord_pcnf

     where  inact eq ' ' and stat eq 'E0001' and

           aufk~aufnr = it_all_ord_pcnf-aufnr.

  endif.

  endif.

select aufpl

    from afko

     into table it_all_ord_link

     for all entries in  it_all_ord_grnk

     where  aufnr = it_all_ord_grnk-aufnr.

select afvc~vornr

    from afvc join jest on afvc~objnr = jest~objnr

    into table it_all_op

    for all entries in it_all_ord_link

    where inact eq ' ' and stat eq 'I0009' and

          afvc~aufpl = it_all_ord_link-aufpl.

[Hvshal4u]

Please check the it_all_ord_link for not initial otherwise it will bring all the records from the table.

Regards,

Vishal

[former_member210823]

It checked,IT is not inisial...

[Former Member]

hi,

  just try this,in your last query where condition inact is not key field,take all records and delete records which are not equal to ' '.

[former_member210823]

it was good sugestion

[Former Member]

That won't help much.

How many records are there in it_ord_ord_link?

Before doing the last SELECT, you should sort that table and DELETE ADJACENT DUPLICATES.

Rob

[Former Member]

Hi Masoumeh,

You can do a performance trace to find out the bottle necks that are caused in your program. Also try to make use of primary fields in your WHERE conditions if possible. The objective should be to filter as much as possible to get your final entries.

Cheers,

Varun

[bhuvneshkumar_gupta]

This message was moderated.

[RaymondGiuseppi]

You could remove some Join and merge some select.

Add a field OBJNR at it_all_ord, you can build OBJNR by concatenation of 'OR' and AUFNR in a LOOP.

CONCATENATE 'OR' wa-aufnr INTO wa-objnr.

Then select only from JEST with whole primary key.

SELECT objnr stat into CORRESPONDING FIELDS OF TABLE it_jest
  from jest
  FOR ALL ENTRIES IN it_all_ord
  WHERE objnr = it_all_ord-objnr
    AND inact EQ ' '
    AND stat IN ('I0010', 'E0001').

Then LOOP at the internal table and create your two internal tables rebuilding AUFNR from OBJNR+2 and a CASE on STAT value

wa-aufnr = wa-objnr+2.
CASE wa-stat.
  WHEN 'I0010'.

For operations you could concatenate 'OV',  AUFPL and APLZL to build OBJNR or build a single SELECT like

SELECT afko~aufnr afvc~vornr
   FROM afko
   JOIN afvc
     ON afvc~aufpl = afko~aufpl
   JOIN jest
     ON afvc~objnr = jest~objnr
   INTO TABLE it_all_op
   FOR ALL ENTRIES IN  it_all_ord_grnk
   WHERE  aufnr = it_all_ord_grnk-aufnr
     AND stat EQ 'I0009'
     AND inact EQ ' '.

But FOR ALL ENTRIES may cause performance problem with huge volumn of data (e.g. if no material selected?) so either insure that user input a list of values (mandatory fields, and use of SELECT_OPTIONS_RESTRICT only allowing I/EQ records) or replace last query using a subquery on first selection, like in following sample

SELECT afko~aufnr afvc~vornr
  FROM afko
  JOIN afvc
    ON afvc~aufpl = afko~aufpl
  JOIN jest AS afko_jest
    ON afko~objnr = jest~objnr
  JOIN jest AS afvc_jest
    ON afvc~objnr = jest~objnr
  INTO TABLE it_all_op
  WHERE afko_jest~stat EQ 'E0001'
    AND afko_jest~inact EQ ' '
    AND afvc_jest~stat EQ 'I0009'
    AND afvc_jest~inact EQ ' '
    AND EXISTS ( SELECT * FROM afpo
        WHERE afpo~aufnr EQ afko~aufnr
          AND matnr IN material ).

Regards,

Raymond

[former_member210823]

it was good sugestion, but for first select I have more than 100000 datas, so the loop is very slowllyyy

[RaymondGiuseppi]

Try to build a single select for  PCNF as I provided for GRNK (also add AUFK to correct my last select, AFKO as no OBJNR field in AFKO but in AUFK) so no more FOR ALL ENTRIES options.

Regards,

Raymond

[NTeunckens]

Hi

You might want to check out the SAP Standard Code from reports such as 'RIAUFK20' or 'RI_ORDER_OPERATION_LIST' as I find a lot of your selection-parameters are provided there.

For instance : When using Trx. "IW49N" you can easily add Material, the OrderHeader and/or Operation-UserStatus and SystemStatus indicating a Confirmed OrderOperation ...

Through debugging you could get some more insight in how these reports fetch the data in an optimized way.

Further, you could look into the use of the Order-wise DB-views such as 'VIAUFK_AFVC' and the like ... DB-view 'VIAUFK_AFVC' joins "AFIH" / "AUFK" / "AFKO" / "AFVC" / "AFVV" / "ILOA" / "AFVU"-tables so this could be an easier way of fetching the bulk of the data ...

Hope this is of any help.

Kind regards

Nic

[former_member241258]

This message was moderated.

[Former Member]

before using FOR ALL ENTRIES, make sure to SORT and DELETE ADJACENT DUPLICATES the entries of the table based on the fields you use in your WHERE condition.

[former_member213851]

If possible, put some additional mandatory fields on input screen.

As a last choice, also think of secondary indexes considering it's negative impact (if any)