topic Re: Performance Issue in Application Development and Automation Discussions

Performance Issue

Former Member — Fri, 19 Aug 2005 12:53:24 GMT

Hi All,

Is any better way to write this query.

iaufnr is containing atleast 10000 records.

CHECK NOT iaufnr[] IS INITIAL.

SORT iaufnr BY aufnr.

DELETE ADJACENT DUPLICATES FROM iaufnr.

SELECT aufnr belnr emtyp

FROM auaa

INTO corresponding fields of TABLE iauaa

FOR ALL ENTRIES IN iaufnr

WHERE aufnr = iaufnr-aufnr.

delete iauaa where emtyp <> 'OR'.

Regards,

Dilip

Message was edited by: Diliip Gupchup

Re: Performance Issue

RichHeilman — Fri, 19 Aug 2005 12:55:55 GMT

Looks ok to me. Does it run a long time? How is iaufnr getting filled?

Regards,

Rich Heilman

Re: Performance Issue

Former Member — Fri, 19 Aug 2005 12:56:24 GMT

If iauaa

contains only 'aufnr' 'belnr' 'emtyp', choose the <b>"into table" iauaa</b> statement.

Regards,

Erwan.

Message was edited by: Erwan LE BRUN

Re: Performance Issue

Former Member — Fri, 19 Aug 2005 12:59:05 GMT

Hi,

SELECT aufnr belnr emtyp

FROM auaa

INTO corresponding fields of TABLE iauaa

FOR ALL ENTRIES IN iaufnr

WHERE aufnr = iaufnr-aufnr and emtyp = 'OR'.

Svetlin

Re: Performance Issue

RichHeilman — Fri, 19 Aug 2005 13:00:35 GMT

I guess you are seeing performance issue because AUAA is a cluster table and you are hitting it with AUFNR which is not part of the key and also you can't create a secondary index on a cluster. The only way to improve would be somehow linking thru BELNR instead of AUFNR.

Regards,

Rich Heilman

Re: Performance Issue

Former Member — Fri, 19 Aug 2005 13:10:34 GMT

hi, I think the reason is iaufnr is too large.

when used in the FOR ALL ENTRIES, the iaufnr will be split to many pieces select requestion and submit to the DB layer. So though the SELECT is only one sentence, actually it access the DB for many times. You can demonstrate in ST05.

In my opinion, you should try your best to reduce the iaufnr size.

you can split the 10000 entries to 5 parts each about 2000 entries.

The other way is select out entries more than you need with simple condition. And filter out the useless entries in the later logic.

Help it will be help.

Thanks a lot

Re: Performance Issue

Former Member — Fri, 19 Aug 2005 13:32:38 GMT

Rich is right. Using an unindexed select on this table will take forever; however, the header table is AUAK and it has a secondary index on the object number. I think if you concatenate 'OR' with the order number (left padded with zeroes) and select from this table, you'll get the document number. You can then use this to quickly get the data from AUAA.

Rob

The following is pretty quick. You'll have to use the "for all entries on the select from auak:


REPORT ztest.

TABLES: auaa, auak.

DATA: order  LIKE aufk-aufnr VALUE '000000100001',
      object LIKE auak-objnr,
      docno  LIKE auak-belnr.

CONCATENATE 'OR' order INTO object.

SELECT SINGLE belnr FROM auak INTO docno
* for all entries in...
  WHERE objnr = object.

SELECT * FROM auaa WHERE belnr = docno.
* ...
ENDSELECT.

Message was edited by: Rob Burbank

Re: Performance Issue

RichHeilman — Fri, 19 Aug 2005 13:36:12 GMT

I was thinking the same as Rob. Give that a try.

Regards,

Rich Heilman

Re: Performance Issue

Former Member — Sat, 20 Aug 2005 07:57:06 GMT

Hi,

I tried with all solutions, but still not able to get the correct output.

Kindly suggest any other solution you know.

Regards,

Dilip

Re: Performance Issue

Former Member — Sat, 20 Aug 2005 10:28:31 GMT

Hi Dilip,

Always FOR ALL ENTRIES will consume a lot of time. For that reason I remember us revamping all our old programs by removing that and instead looped it into that Internal table.

Also, since the field on the selection condition doesn't form a part of the key fields, it will select lot of unwanted records and that takes a lotta time.

The best you can do is,

CHECK NOT iaufnr[] IS INITIAL.

SORT iaufnr BY aufnr.

DELETE ADJACENT DUPLICATES FROM iaufnr.

LOOP AT WA_IAUFNR.

SELECT aufnr belnr emtyp

FROM auaa

INTO corresponding fields of TABLE iauaa

WHERE aufnr = WA_iaufnr-aufnr.

ENDLOOP.

delete iauaa where emtyp <> 'OR'.

Try this, and I'm sure it'll take less time when compared to the earlier code.

Cheers,

Sam.

Re: Performance Issue

Former Member — Sat, 20 Aug 2005 19:17:06 GMT

Dilip - the following code runs quickly in my system:


REPORT ztest.

TABLES: auaa, auak.

DATA: object LIKE auak-objnr,
      docno  LIKE auak-belnr.
DATA: BEGIN OF iaufnr OCCURS 0.
        INCLUDE STRUCTURE aufk.
DATA: END   OF iaufnr.

SELECT aufnr
  FROM aufk
  UP TO 10 ROWS
  INTO CORRESPONDING FIELDS OF TABLE iaufnr.

LOOP AT iaufnr.
  CONCATENATE 'OR' iaufnr-aufnr INTO object.

  SELECT SINGLE belnr FROM auak INTO docno
    WHERE objnr = object.

  SELECT * FROM auaa WHERE belnr = docno.
    WRITE: /001 auaa-belnr, auaa-lfdnr, auaa-emtyp.

  ENDSELECT.
ENDLOOP.

The only problem I can see is if there is if AUAK-OBJNR is not mapped in your system the way I have shown here.

If you still have problems after this, tell us what output or error you are getting: also what AUAK-OBJNR looks like in your system.

Rob

Re: Performance Issue

Former Member — Mon, 22 Aug 2005 10:49:51 GMT

Hi Dillip,

try with this

CHECK NOT iaufnr[] IS INITIAL.

SORT iaufnr BY aufnr.

DELETE ADJACENT DUPLICATES FROM iaufnr.

********************

delete iaufnr where aufnr is initial.

********************

SELECT aufnr belnr emtyp

FROM auaa

INTO corresponding fields of TABLE iauaa

FOR ALL ENTRIES IN iaufnr

WHERE aufnr = iaufnr-aufnr.

delete iauaa where emtyp <> 'OR'.

Cheers,

Sasi

Re: Performance Issue

Former Member — Mon, 22 Aug 2005 12:03:03 GMT

Hello,

WHy don;t you try creation of a range for aufnr and then try pass it. I bet it will be very fast.

Regards,

Shekhar Kulkarni