topic Re: performence problem in Application Development and Automation Discussions

performence problem

Former Member — Fri, 19 Feb 2010 13:22:56 GMT

Hi All,

I have written a select command on the table COEP based on objnr and vrgng fields ( both are required )

So when i was execuiting the query it was taking too much time for a single record.

Can you guide me the alternative table or alternative how to get the data fastly.

Advanced Thanks for your guidence

Best Regards

Sudhakar

Re: performence problem

Former Member — Fri, 19 Feb 2010 13:31:17 GMT

There are several options to speed up your query.

- Try to get the key fields. Maybe through COBK.

- See if there's an index defined for your fields. If not, it can be useful to create it. But be careful: adding indices can slow down other actions on the COEP table (like updates).

- Select only the fields that you need (don't use SELECT *)

- Don't use a negative SELECT (as it invokes a full table scan)

With kind regards,

Roel van den Berge

Edited by: Roel van den Berge on Feb 19, 2010 2:31 PM

Re: performence problem

Former Member — Fri, 19 Feb 2010 13:31:45 GMT

Do like if not matching key fields

Loop at itab into wa_tab.

clear itab_coep.

SELECT * FROM COEP INTO itab_coep WHERE OBJNR EQ wa_tab-objnr

APPEND itab_coep to itab_coep1.

delete itab_coep1 where vrgng NE wa_tab-vrgng .

ENDLOOP.

Edited by: Sachin Bidkar on Feb 19, 2010 2:32 PM

Re: performence problem

Former Member — Fri, 19 Feb 2010 13:40:51 GMT

Hi All,

Thank you for your speed reply.

But still it is taking the time after using the objnr ( index field )

Kindly guide me if any alternative.

Thanks in advance

Best Regards

Sudhakar

Re: performence problem

Former Member — Fri, 19 Feb 2010 13:42:26 GMT

can you paste your code for that plz

Re: performence problem

Former Member — Fri, 19 Feb 2010 13:44:29 GMT

It may be an index field, but that doesn't mean the index is being used. You can verify this by running an ST05 trace (using the SQL Explain functionality). You'll need an index with both objnr and vrgng probably. Alternatively you could try to find a way to fetch the other index fields in order to use an existing index.

Re: performence problem

RaymondGiuseppi — Fri, 19 Feb 2010 13:58:13 GMT

Take a look at [Note 115219 - Performance improvements for line items|https://service.sap.com/sap/support/notes/115219]

Regards,

Raymond

Re: performence problem

Former Member — Fri, 19 Feb 2010 14:00:19 GMT

Hi All,

Please find code below :

SELECT wtgbtr objnr vrgng

FROM coep

INTO TABLE itb_coep

FOR ALL ENTRIES IN itb_aufk

WHERE objnr = itb_aufk-objnr

and vrgng ne 'KOAO'.

The above query is taking too much time to fetch the single record .

Thank you for your guidence

Regrds

Sudhakar

Re: performence problem

Former Member — Fri, 19 Feb 2010 14:13:09 GMT

for all entries it will take time...

do like this i mentioned earlier

Loop at itb_aufk into wa.

Clear itb_coep.

SELECT wtgbtr objnr vrgng

FROM coep

INTO TABLE itb_coep

WHERE kokrs eq wa-kokrs

objnr = wa-objnr.

APPEND LINES OF itb_coep TO itb_coep1.

ENDLOOP.

DELETE itb_coep1 WHERE vrgng ne 'KOAO'.

i tried this code its working fast...

Re: performence problem

RaymondGiuseppi — Fri, 19 Feb 2010 14:14:56 GMT

- Check if index COEP~2 is active in (all) database

- Do you need COEP or can you use total tables like COSS and COSP which are cumulated but have much less items.

Regards,

Raymond

Re: performence problem

Former Member — Fri, 19 Feb 2010 14:15:49 GMT

This is a frequently asked question. If you had searched, you would have found something like:

SELECT wtgbtr objnr vrgng
  FROM coep
  INTO TABLE itb_coep
  FOR ALL ENTRIES IN itb_aufk
  WHERE objnr = itb_aufk-objnr
    AND lednr = '0'
    AND vrgng NE 'KOAO'.

Please use code tags when posting code.

Rob