How to accelerate these select.

[Former Member]

Hello goeroes,

I have a programm with a select which consume to much memeoy time (and real time) and must be speeded up.
This select takes up 98,5 percetn of the lonnnggg running time.

may be some of you know what to do increase the performance. I do not see any possibilities anymore.

The select is on transparant tabel AFRU (>7M entries) with inner join to AUFK (for LOEKZ).

SELECT afru-ersda  afru-erzet afru-aufnr
FROM afru INNER JOIN aufk on aufk-aufnr = afru-aufnr

INTO CORRESPONDING FIELDS OF TABLE gt-afru

WHERE afru~stokz eq ''
AND       afru~stzhl eq ''
AND       aufk~loekz eq ''
AND       afru~ersda in so_ersda
AND      afru~werks IN so_werks

AND     afru~aufnr IN so_aufnr
AND     aufk~auart IN so_auart

as you can see i do not use any primary key fields.
there is an secondary index with ersda and werks (and 2 other fields) in afru.

Who can help me out with this.

Thanks in advnce

[adam_krawczyk1]

Hi Frank,

It looks like query takes long time because there are no good indexes.

You write that there is already index on ERSDA and WERKS, however these two fields are not very distinctive as I guess you can have many (thousands?) of entries for single confirmation date (ERSDA) and Plant (WERKS). That is why index does not filter result enough good, as database need to scan all rows from that index subset.

Try to create index on fields which will give you immedietely narrow results, like AFRU~AUFNR. I guess there are not much rows in AFRU table with same work order number.

I would suggest to perform these steps (or play with similar options):

1) Create index on AUFNR in AFRU.

2) Execute SELECT INTO TABLE on AFRU table only.

3) Execute next SELECT FROM AUFK FOR ALL ENTRIES in table from point 2.

Although it is recommended to use JOIN, sometimes it is worth to split statement into two separated, especially if first SELECT will return narrow result that will be used in second statement.

You can also try to switch order of points 2 and 3 - first select on AUFK, then AFRU (depending on how results are retrieved). Do measurements with ST05 or SAT/SE30 transaction to compare your solutions efficiency.

Regards

Adam

[krishna_k19]

Hi,

    Instead of using Joins , pls use "For all entries" it will give better result.And select first from AUFK nd then AFRU, y bcz AUFK is a master table & 2nd is item.

And don't forget to check whether the first table is initialized or not.

Rgds,

Krishna

[adam_krawczyk1]

Hi Krishna,

Good suggestions. Only I would not be so sure if we need to select first from AUFK - I would say rather opposite that from AFRU, but everything depends on result set so it is good to compare two solutions.

As we see in current SELECT statement there are 4 filters in WHERE for AFRU (STOKZ, STZHL, ERSDA and WERKS) and only two for AUFK table (LOEKZ, AUART). It means that probably query on AFRU will filter entries better from so_aufnr. In AUFK table all entries will be found from AUFNR which is the key, only two filters on LOEKZ and AUART may limit result. Of course everything depends on data distribution in these two tables, as results may be also opposite - 2 filters on AUFK works better than 4 filters on AFRU.

It is important that first select statements filters rows more, finally we need to retrieve less rows that we pass to next SELECT statement - this is time which we gain.

Regards

Adam

[kesavadas_thekkillath]

"Instead of using Joins , pls use "For all entries" it will give better result" - Do not be so sure 🙂

Please read

[krishna_k19]

If its clear pls close the thread.

Rgds,

Krishna

[Former Member]

Thanx guys,

For your help.

I have added an index.

I have split the select
Or better said i have diffrent splitted selects depending on the content of the selection-options parameters..

I do not use STOKZ, STZHL anymore in select iotself.. I delete them from the itab (made by selects)
I see the performance is increased (not as much i wanted) and i shall do some more tuning to it.
but it is a good start.

[Former Member]

Hi,

Use FOR ALL ENTRIES instead of joins...

Regards,

Shaiksha Vali.

[matt]

As Kesavadas Thekkillath said

Do not be so sure 🙂
Please read For All Entries is NOT better than INNER JOIN in most cases