02-27-2015 6:48 AM
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
02-27-2015 10:29 AM
02-27-2015 8:17 AM
02-27-2015 8:19 AM
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?)
02-27-2015 9:04 AM
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
02-27-2015 9:22 AM
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.
02-27-2015 10:25 AM
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
02-27-2015 10:32 AM
02-27-2015 10:29 AM
02-27-2015 10:31 AM
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.
02-27-2015 10:39 AM
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
02-27-2015 11:12 AM
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
02-27-2015 1:07 PM
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.
02-27-2015 2:06 PM
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
02-27-2015 5:06 PM
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!
03-02-2015 5:52 AM
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
03-02-2015 8:14 AM
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.
03-02-2015 8:29 AM
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
03-02-2015 8:31 AM
Dear K Kiran ,
I know that indexes slow down the entry. I will not create index more.
Thanks,
Aneel