Performance issue on table EKPO

[Former Member]

There is a performance issue for table EKPO.

We have matnr, werks and loekz fields to match.

We dont have the key fields ebeln and ebelp.

The query is as follows:

select ebeln ebelp matnr werks

from ekpo

where matnr in s_matnr

and werks in s_werks

and loekz ne 'L'.

Index 1 for this table is:

matnr, werks, bstyp, loekz, elikz, matkl

Since we dont have bstyp. would the query fetch data based on index1.

Or should we remove 'loekz' from the where clause, populate internal table based on matnr, werks and Later delete the entries from internal table based on loekz.

Would this improve the performance ?

[Former Member]

There is a performance issue for table EKPO.

We have matnr, werks and loekz fields to match.

We dont have the key fields ebeln and ebelp.

The query is as follows:

select ebeln ebelp matnr werks

from ekpo

where matnr in s_matnr

and werks in s_werks

and loekz ne 'L'.

Index 1 for this table is:

matnr, werks, bstyp, loekz, elikz, matkl

Since we dont have bstyp. would the query fetch data based on index1.

Or should we remove 'loekz' from the where clause, populate internal table based on matnr, werks and Later delete the entries from internal table based on loekz.

Would this improve the performance ?

[Former Member]

Hi,

The order of the fields in the index is very important for the accessing speed.

If u create another index using fields matnr, werks, loekz then it really improve ur program Speed.

Regards,

Digesh Panchal

[FredericGirod]

Hi,

a delete of entries in internal table is very fast. But, you have to check the size of the internal table, if you select without restrict the LOEKZ.

I prefere the delete solution, but, that could be dangerous for the memory.

Rgd

Frédéric

[Former Member]

First and foremost with select stmt it will try to find the best primary or alternative index. in ur case as it doesn't match the index 1 it will still go sequentially and will degrade the performance.

two options.

1. remove the field loekz and get all the data (hope not huge) into internal table and then process as handling internal table is fast.

2. Create alternate index but I am not sure if this is allowed as it also creates additional buden of database as it needs to keep all the alternate index update everytime a new entry happens or when deleted.

[Former Member]

Missed one more point. u could use SQL trace to check if the index used by the system is the one which u want else force the DB to select the one which u want.

[former_member181966]

Check out the OSS note # 561331.

Also go to t-code St02 and st03 ... to check table performance...

Hope this`ll help you .

Thanks

[RichHeilman]

I have seen this make a big difference. Reverse the LOEKZ field. THe system performs better when using EQ instead of NE.

select ebeln ebelp matnr werks
        from ekpo
            where matnr in s_matnr
              and werks in s_werks
              <b>and loekz eq space.</b>

REgards,

Rich Heilman