Performance Issue

[Former Member]

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

[RichHeilman]

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

Regards,

Rich Heilman

[Former Member]

If iauaa

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

Regards,

Erwan.

Message was edited by: Erwan LE BRUN

[RichHeilman]

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

[Former Member]

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

[RichHeilman]

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

Regards,

Rich Heilman

[Former Member]

Hi,

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

Kindly suggest any other solution you know.

Regards,

Dilip

[Former Member]

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.

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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