topic performance in select statement in Application Development and Automation Discussions

performance in select statement

Former Member — Fri, 11 Dec 2009 00:46:15 GMT

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.

Re: performance in select statement

Former Member — Fri, 11 Dec 2009 02:50:15 GMT

Hi.

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

Nag

Re: performance in select statement

Former Member — Fri, 11 Dec 2009 06:16:35 GMT

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.

Re: performance in select statement

Former Member — Fri, 11 Dec 2009 06:28:17 GMT

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.

Re: performance in select statement

Former Member — Fri, 11 Dec 2009 14:33:34 GMT

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

Re: performance in select statement

Former Member — Fri, 11 Dec 2009 16:36:32 GMT

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.

Re: performance in select statement

bettinasilveira — Sun, 13 Dec 2009 00:16:28 GMT

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.

Re: performance in select statement

Former Member — Mon, 14 Dec 2009 16:50:50 GMT

> 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.

Re: performance in select statement

Former Member — Tue, 15 Dec 2009 09:19:27 GMT

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

Re: performance in select statement

Former Member — Tue, 15 Dec 2009 14:20:16 GMT

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