performance in select statement

[Former Member]

hi all,

i am trying to fetch data from coep table by using for all entries.it is taking a lot of time.when i gave the same values in the se16 of that table it took time but not the same time as in the program,below is my select statement.

if not obj_t[] is initial.

select kokrs

belnr

buzei

perio

wkgbtr

objnr

gjahr

kstar

bukrs

into table it_assign_t

from coep

for all entries in obj_t

where perio in s_period

and objnr = obj_t-objnr.

endif.

i had created a secondary index for this two fields in the same order in the coep table even than it is taking huge time.

[Former Member]

Hi.

Run SQL trace and check whether select query is picking index or not.

Nag

[Former Member]

Hi,

In first scenario,as you are not using key fields,so it will take time.Your approach of creating index is right in order to solve the problem.But I guess you have create index with the fields in order : perio ,objnr.

This 'perio' field has range from 1 to 999.So although index is helpful,still a lot of records have to be searched for your query.I would advice you to delete the current index and create a new one with the fields in order :

mandt

objnr

perio

As there will be very few records with objnr same as provided by ur select statement.Performance will improve

Hope it helps.

[Former Member]

hi,

thanks for the reply,sorry i pasted the code before the change,infact i had given objnr than perio later in the where condition.even than the performance is still worst.

[Former Member]

Your secondary index is entirely unnecessary. The first thing you should do is get rid of it. Then add LEDNR to your SELECT. If you look at the domain for that field, you'll see that it is always '00'. Thius should use index COEP~1 which already exists:

IF NOT obj_t[] IS INITIAL.
  SELECT kokrs belnr buzei perio wkgbtr objnr gjahr kstar bukrs
    INTO TABLE it_assign_t
    FROM coep
    FOR ALL ENTRIES IN obj_t
    WHERE lednr = '00'
      AND objnr = obj_t-objnr
      AND perio IN s_period.
ENDIF.

Rob

[Former Member]

hi,

thanks for the reply,it was working absolutely fine.i just wants to ask,how did u analyze this,i mean you expereinced same problem before ort you analysed,so that i just wants to think the same way in the future,:) thanks alot for the answer

[Former Member]

Well, the same question has come up before in the forum, so I knew at the beginning what the problem and answer was.

And yes, I think I had the same problem some time ago and this is how I solved it. The basic idea is to go out of your way to find and use an existing index if you are having performance problems with a SELECT. This is not always possible, but should be tried.

Thanks for the update.

Rob

[Former Member]

Hi ,

Delete adjacent duplicates in obj_t[] internal table.

Also, use ST05 and SE30 for your analysis.

creating a secondary index is always not the solution , check the

selection parameters if you can change use for fetch of data from table.

[bettinasilveira]

If the above solutions didn't help, you can try doing a range with the values of obj_t and using this instead the for all entries statement.

[Former Member]

> you can try doing a range with the values of obj_t and using this instead the for all entries statement

better not.

Rob gave already a solution, all others do not change the index support.