topic Re: Program - indexes in Application Development and Automation Discussions

Program - indexes

Former Member — Fri, 20 Jun 2008 05:16:38 GMT

Dear friends,

Here the major problem is, even though the reports made use of primary and secondary indexs, they are going to sequential read instead of direct read and the performance is badly affected.

Oracle is used as SAP database.

Kindly suggest.

Praveen Lobo

Re: Program - indexes

Former Member — Fri, 20 Jun 2008 05:43:02 GMT

Hi,

With this input we cant suggest you anythg. Eloborate on your problem or post the code snippet here.

Regards,

Madhu

Re: Program - indexes

Former Member — Fri, 20 Jun 2008 07:44:42 GMT

Serquential and direct read says nothing about performance, the difference is only that you read more records in a sequence instead of only once, as in SELECT SINGLE.

Of course it takes longer if you read more, but you say that you need then you have to pay for it.

SQL trace:

/people/siegfried.boes/blog/2007/09/05/the-sql-trace-st05-150-quick-and-easy

Check you SQL statement summary, if the minimal time per record is larger 5000 micro seconds, then you should indexes.

You should have a look at the number of records, which are read, if this is large, then you should rethink whether you really need that many records.

Siegfried

Re: Program - indexes

Former Member — Mon, 23 Jun 2008 10:00:54 GMT

Hello,

When i check in ST05, indexes are properly used (in Explain button). But when i check in SM50 during execution all sql statements are read sequentially (even though indexes are used in the statements), cause poor performance.

The below statement uses primary index, but shows sequential read during execution.

SELECT matnr maktx

INTO TABLE it_makt

FROM makt

FOR ALL ENTRIES IN it_main

WHERE matnr EQ it_main-matnr and spras = 'E'.

Kindly suggest.

Praveen Lobo

Re: Program - indexes

alison_lloyd — Mon, 23 Jun 2008 14:46:27 GMT

do the indexes exist on oracle - check in se11

have you sumarised by table access and aggregated by table in st05 to see exactly which table is taking all the time?

Have you checked on explain sql for all accesses to the table?

Re: Program - indexes

Former Member — Mon, 23 Jun 2008 16:56:39 GMT

hi,

One tip for performance tuning: For ex. if you are using the query as U mentioned on MARA and using for all entries.

The sys searches for all the fields within the internal table.

Define an int table say it_matnr with one field MATNR.

pass all the MATNRs from it_it_main into it_matnr

and then use in the query.

Reward suitably, if this helps.

Rgds,

Raghu.

Re: Program - indexes

alison_lloyd — Tue, 24 Jun 2008 12:47:42 GMT

are there duplicate material numbers in it_main if so define it_main_aux, populate with it_main entries sort by matnr and delete adjacent duplicates using matnr then use this as your for all entries table

Re: Program - indexes

Former Member — Tue, 24 Jun 2008 13:05:38 GMT

> The below statement uses primary index, but shows sequential read during execution.

what do you really read in the ST05, go to the summary by SQL statement.

That does the line for this statement tell you

duration

executions

records

mintime/rec ?

Check this numbers then I can see what you mean with bad performance!

Siegfried