Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Abap: EKPO table

aneel_munawar
Participant
0 Kudos

Dear Abap experts,

I am running this simple query on the EKPO table but it takes more  than 20 minutes.

SELECT * INTO CORRESPONDING FIELDS OF TABLE it_ekpo

           FROM ekpo

          WHERE lgort = '1003'.

I checked ST05 and noted that others users are accessing EKPO table. But when no user is accessing this table then this query runs fast. How can I overcome this problem. So that we could get the report also during data entry time.

I want to also mention that even SE11 not works and gives output very slow.

Regards,

Aneel

1 ACCEPTED SOLUTION

Former Member
0 Kudos

create Secondary Index on table EKPO for field LGORT.

17 REPLIES 17

former_member1200644
Participant
0 Kudos

This message was moderated.

Phillip_Morgan
Contributor
0 Kudos

Aneel,

Look at EKPO in SE11 (if you can) and you will see that LGORT is not a key field. This means that your query will do a full table scan.

Look through the existing indexes (unless there are custom ones) and you will see that there is no index on LGORT.

Full table scan. If there are a lot of entries that can take a long time.

The fact that the query runs faster when no one is accessing the table sounds like it is taking advantage of cached information (I may be wrong).

You need to reduce the dataset if possible (without LGORT).

Bring the data into an internal table then reduce by LGORT.

Reason with functional person to understand how to reduce the set (EKORG? EKGRP?)

0 Kudos

Dear Philip,

I have no other option to apply the LGORT filter. EKORG and  EKGRP fields exist in the EKKO table. but I can not use these. Becasue If I get the orders then in the item table , order can have more than one lgort's records.


Regards,

Aneel

0 Kudos

Yes, but you are using indexes which is faster. Then it is true that you will have more than one LGORT in your internal table but you exclude the ones you do not want with

DELETE itab where LGORT <> 'your LGORT'

fast because in memory.

rajkumarnarasimman
Active Contributor
0 Kudos

Hi Aneel,


Aneel Munawar wrote:

Dear Abap experts,

I am running this simple query on the EKPO table but it takes more  than 20 minutes.

SELECT * INTO CORRESPONDING FIELDS OF TABLE it_ekpo

           FROM ekpo

          WHERE lgort = '1003'.

Here you are using INTO Corresponding Fields, that shows that you are need only limited fields into internal table from database, but in select query * is mentioned.  Kindly mention the required fields which is same as structure defined in Select statement as shown below.


types: begin of ty_ekpo,

               ebeln type ebeln,

               ebelp type eblelp,

          end fo ty_ekpo.

data: it_ekpo type table of ty_ekpo

select ebeln

          ebelp

          from ekpo

          into table it_ekpo

          where lgort = '1003'.

Regards

Rajkumar Narasimman

0 Kudos

Dear Rajkumar ,

I need all fields.


Regards,

Aneel

Former Member
0 Kudos

create Secondary Index on table EKPO for field LGORT.

Former Member
0 Kudos

Hi Aneel,

You can increase the performance for the above Statement by

a) Use into table (Remove MOVE-CORRESPONDING)

b) Create a secondary index with LGORT.

Thanks and Regards,

S.Rajendranath.

0 Kudos

Dear S.Rajendranath.,

How much it will affect the data entry, if i apply the index on LGORT.

I mean that I don't want to slow down  the data entry for EKPO table.

Regards,

Aneel

former_member252769
Active Participant
0 Kudos

Aneel,

EKPO is a huge table to you have to reduce the dataset by introducing WERKS in selection criteria as LGORT is always associated with plants, this will faster your select query.

Cheers

kiran_k8
Active Contributor
0 Kudos

Aneel,

1.EKPO is a transaction data and is bound to have huge amount of data.So,test runs in D and Q where the amount of data is very less,is not the benchmark to come to a conclusion on performance.If Q is regulary refreshed with production data then it will help you in analysing the performance.

2.Why do you need the whole EKPO data with a specific Storage Location ? Discuss with your FC and get the exact requirement and then code keeping in mind the suggestions you got in the forum.

K.Kiran.

Former Member
0 Kudos

Unless you have a very large number of storage locations, creating an index on LGORT will not be very selective and therefore not help very much, if at all.

Sometimes you just have to bite the bullet and accept that the query will be slow.

Rob

Former Member
0 Kudos

Add the following conditions in where clause:

EBELN BETWEEN '0000000001' AND '9999999999' AND

EBELP BETWEEN '00001' AND '99999'

Instead of giving 1 to 9999... you can find out the number ranges for EBELN and EBELP from SNRO and put them in the conditions. Like for e.g., PO number starting from 4500000001 to 4599999999.

This way you are using the primary key fields, and so primary index would be picked up by the db optimizer, making the query efficient.

Also, if you want to select all fields, why are using 'into corresponding'. You can just write 'into table'?!

Cheers!

aneel_munawar
Participant
0 Kudos

Dear All,

Issue is being closed. As suggested by Mr mayank singh, creating index on the LGORT solved my problem. Thanks to all for help and suggestions.

Regards,

Aneel

0 Kudos

Aneel,

Tomorrow you will write one more query with different selection criteria which may also take time.So,you will go ahead create an INDEX for that also ?

Not the right approach to decide on creation of INDEX.

You need to discuss with Functional and get the requirements properly.To be frank the more you question the Functional the more inputs you will get.Did you asked the FC why he requires the whole data from EKPO for that LGORT without any year,company code and plant validation?

K.Kiran.

0 Kudos

Dear K.Kiran,

Yes, You are right but they are using this location field in the reference field of VA03 Accounting TAB. When they define a project they  add this location in this field. That's why we need all this data against the storage location.

Regards,

Aneel

0 Kudos

Dear K Kiran ,

I know that indexes slow down the entry. I will not create index more.

Thanks,

Aneel