topic Performance issue on table EKPO in Application Development and Automation Discussions

Performance issue on table EKPO

Former Member — Fri, 10 Feb 2006 10:46:52 GMT

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 ?

Re: Performance issue on table EKPO

Former Member — Fri, 10 Feb 2006 11:00:08 GMT

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

Re: Performance issue on table EKPO

FredericGirod — Fri, 10 Feb 2006 11:03:58 GMT

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

Re: Performance issue on table EKPO

Former Member — Fri, 10 Feb 2006 11:23:30 GMT

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.

Re: Performance issue on table EKPO

Former Member — Fri, 10 Feb 2006 11:25:46 GMT

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.

Re: Performance issue on table EKPO

former_member181966 — Fri, 10 Feb 2006 14:17:34 GMT

Check out the OSS note # 561331.

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

Hope this`ll help you .

Thanks

Re: Performance issue on table EKPO

RichHeilman — Fri, 10 Feb 2006 14:20:04 GMT

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