performance issue

[Former Member]

Hi,

I am working on a performance issue.when i checked in ST04 transaction for one of the select statement,it is showing estimated costs as 487.

When I checked in the program that particular select statement,i found that the index is not created on the fields used in the where condition.

given below is the select statement.

SELECT

matnr

charg

sobkz

kunnr

lfgja

lfmon

kulab

FROM msku

INTO TABLE t_msku

FOR ALL ENTRIES IN t_msku_key

WHERE

matnr IN s_matnr

AND werks IN r_werks

AND sobkz IN r_sobkz

AND kunnr = t_msku_key-kunnr

I have checked in DB05 transaction whether it is feasible to create an index on those fields(werks,sobkz,kunnr).

I found the values for werks as 42.126 between 1-10

and 8.923 between 11-100

616 between 101-1.000

sobkz 51.252 between 1-10

8.753 between 11-100

586 between 101-1.000

kunnr 470.434.

is it feasible to create an index on these fields?

[ThomasZloch]

The index needs just MANDT and KUNNR as key fields, since you have an EQ-condition for KUNNR, plus the field is highly selective.

Thomas

[P561888]

Hi,

Try to use the primary key in the select ,or if not create the Index with fields

Regards,

Bharani

[former_member194613]

hmmm, Thomas here I would not support your answer.

I don't know the table and have no system where is really filled, but the table name is

Special Stocks with Customer

There are probably only records for a few customers, KUNNR must not be highly selective in every table.

The key fields are

MANDT

MATNR

WERKS

CHARG

SOBKZ

KUNNR

so charg is missing. Maybe CHARG could be added to the select.

But most important your select is from the coding, because of the in-clauses:

WHERE

matnr IN s_matnr

AND werks IN r_werks

AND sobkz IN r_sobkz

AND kunnr = t_msku_key-kunnr

But you need to check the statement going to the database, maybe the IN-clauses

are not all used !!!!

Better use ST05, and check time, details and explain, instead of costs in the cursor cache!!!

Siegfried

[ThomasZloch]

Hi Siegfried,

I based my recommendation on the DB05 values provided:

> kunnr 470.434.

Sounds like many distinct values for KUNNR and thus a high selectivity. Question is, whether this figure applies to KUNNR only or to the full primary key down to field KUNNR. If it is the former, I think my recommendation might work OK. Only the original poster can clarify. What is your take on this?

Cheers

Thomas

[Former Member]

Hi Thomas,

Thank you.

Should the index be created on fields having more number of distinct values?

Now can i create an index on matnr,werks,sobkz and kunnr?

[ThomasZloch]

> Should the index be created on fields having more number of distinct values?

yes, imho.

do me a favor and run DB05 again with option "analysis for specified fields" and

1) only for kunnr

2) for matnr,werks,sobkz and kunnr

please post both results here.

Thomas

[valter_oliveira]

Should the index be created on fields having more number of distinct values?

Yes

Now can i create an index on matnr,werks,sobkz and kunnr?

Why? If s_matnr is filled, you won't need an index at all ! And that index will not work too.

I would create the index in kunnr that Thomas proposed if you don't fill s_matnr.

Regards,

Valter Oliveira.

[Former Member]

Hi Thomas,

When I checked in DB05 with only kunnr field,it is showing following values.

distinct values 1-10 11-100 101-1.000 1.001-10.000

kunnr 7.745 2.340 3.773 1.631 1

when i checked for matnr,werks,sobkz and kunnr fields,it is showing following values.

distinct values 1-10 11-100 101-1.000 1.001-10.000

matnr 10.426 8.082 1.726 515 103

werks 51.665 42.126 8.923 616

sobkz 51.665 42.126 8.923 616

kunnr 460.505 460.264 240 1

[ThomasZloch]

I think Siegfried's main concern was that KUNNR might not be very selective, but

> kunnr 7.745

sounds pretty good, so I would go for the MANDT / KUNNR index.

Cheers

Thomas

[former_member194613]

> Question is, whether this figure applies to KUNNR only or to the full primary key down to field KUNNR.

I have not used the DB05 before, it is quite interesting, gives you even an idea of the distribution.

The displayed information is distinct values of all the fields cumulative, i.e. quite different than the display in the explain. It should not confused.

MANDT KUNNR is probably not standard index, because it can be selective like here but it must not be selective.

Siegfried