topic Re: increase speed up program in Application Development and Automation Discussions

increase speed up program

former_member210823 — Mon, 04 Jan 2016 04:15:59 GMT

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.

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.

Re: increase speed up program

Hvshal4u — Mon, 04 Jan 2016 04:26:49 GMT

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

Regards,

Vishal

Re: increase speed up program

former_member210823 — Mon, 04 Jan 2016 04:51:04 GMT

It checked,IT is not inisial...

Re: increase speed up program

Former Member — Mon, 04 Jan 2016 05:02:43 GMT

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 ' '.

Re: increase speed up program

former_member210823 — Mon, 04 Jan 2016 05:18:55 GMT

it was good sugestion

Re: increase speed up program

Former Member — Mon, 04 Jan 2016 15:06:27 GMT

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

Re: increase speed up program

Former Member — Mon, 04 Jan 2016 18:27:17 GMT

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

Re: increase speed up program

bhuvneshkumar_gupta — Mon, 04 Jan 2016 19:07:03 GMT

This message was moderated.

Re: increase speed up program

RaymondGiuseppi — Tue, 05 Jan 2016 10:35:23 GMT

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

Re: increase speed up program

former_member210823 — Tue, 05 Jan 2016 11:05:16 GMT

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

Re: increase speed up program

RaymondGiuseppi — Tue, 05 Jan 2016 12:10:26 GMT

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

Re: increase speed up program

NTeunckens — Tue, 05 Jan 2016 12:55:08 GMT

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

Re: increase speed up program

former_member241258 — Sat, 09 Jan 2016 05:32:11 GMT

This message was moderated.

Re: increase speed up program

Former Member — Sat, 09 Jan 2016 14:41:10 GMT

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.

Re: increase speed up program

former_member213851 — Sat, 09 Jan 2016 17:37:20 GMT

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)