Performance Tuning for A016 (cluster table) Query

[Former Member]

Dear SDNers,

Issue:

The report runs successfully sometimes and sometimes the report gets timed out.

My findings

Query which is taking a long time to execute is the query on A016(cluster Table) to fetch the condition details

SELECT kschl

evrtn

evrtp

knumh

FROM a016 INTO TABLE t_a016

FOR ALL ENTRIES IN t_ekpo

WHERE

kappl EQ c_m (Application)

AND kschl IN s_kschl

AND evrtn = t_ekpo-ebeln

AND evrtp = t_ekpo-ebelp.

where t_ekpo contains entries (4354)from ekko and ekpo based on the selection screen entries.

I see that a016 is a cluster table that is being used here and t_a016 contains (390*) records after the above fetch.

Dear readers,Please help me as to what has to be taken care of inorder to fine tune this select query?

what are the things that i need to make corrections in.

How can i make this fetch effective and faster?

Please help me with your inputs.

Regards,

SuryaD.

Edited by: SuryaD on Oct 26, 2009 6:29 PM

[Former Member]

Dear SDNers,

Issue:

The report runs successfully sometimes and sometimes the report gets timed out.

My findings

Query which is taking a long time to execute is the query on A016(cluster Table) to fetch the condition details

SELECT kschl

evrtn

evrtp

knumh

FROM a016 INTO TABLE t_a016

FOR ALL ENTRIES IN t_ekpo

WHERE

kappl EQ c_m (Application)

AND kschl IN s_kschl

AND evrtn = t_ekpo-ebeln

AND evrtp = t_ekpo-ebelp.

where t_ekpo contains entries (4354)from ekko and ekpo based on the selection screen entries.

I see that a016 is a cluster table that is being used here and t_a016 contains (390*) records after the above fetch.

Dear readers,Please help me as to what has to be taken care of inorder to fine tune this select query?

what are the things that i need to make corrections in.

How can i make this fetch effective and faster?

Please help me with your inputs.

Regards,

SuryaD.

Edited by: SuryaD on Oct 26, 2009 6:29 PM

[Former Member]

Hi,

1) Try to hit the table A016 with only unique entries of t_ekpo for EBELN and EBELP...

Use some temp table lt_ekpo = t_ekpo

i.e. sort lt_ekpo by ebeln ebelp

delete adjacent duplicates from lt_ekpo comparing ebeln ebelp.

Now write you select query as below...So that the table might be hit with less number of entries...

SELECT kschl

evrtn

evrtp

knumh

FROM a016 INTO TABLE t_a016

FOR ALL ENTRIES IN lt_ekpo

WHERE

kappl EQ c_m (Application)

AND kschl IN s_kschl

AND evrtn = lt_ekpo-ebeln

AND evrtp = lt_ekpo-ebelp.

Please note that there will be no change in the output data if you hit with duplicate entries/unique as FOR ALL ENTRIES will fetch only unique entries matching the where condition.

2) Order of where conditon fields should be same as the order in table for better performance...

3) If you find any index on the table, try to include the fields in where condition(if possible) for better performance

Hope this helps

Regards

Shiva

[Former Member]

Hi Shiva,

Thanks for your reply.

I shall write the query like you mentioned.

True, i can delete the duplicate entries from lt_ekpo since it contains many records due to the presence of the line items for every individual PO.

This infact redues the no.of records to a minimum.

1. And this will not affect my fetch in the query on A016 right?

2. I lookd for if any indexes are available on a016(pooled table) and there are no indexes available on the same.

Thanks for your input Shiva.

Regards,

SuryaD.

[ThomasZloch]

Pooled tables cannot have secondary indexes. Order of fields in where-clause does not influence performance. Next to removing duplicates you should also make sure that the entries in your internal table are sorted by ebeln and ebelp.

The actual problem is S_KSCHL however. If this contains many entries or is left empty, then your primary key access is not very efficient.

Thomas

[Former Member]

hi Thomas,

Thanks for the additional input.

and i shall ensure that the internal table is sorted by ebeln and ebelp.

To answer your statement on "The actual problem is S_KSCHL however. If this contains many entries or is left empty, then your primary key access is not very efficient", Please help me understand as to how the s_kschl can be a problem here.

The user says that sometimes they are able to run the report and sometimes it gets timed out.

They however have entries for s_kschl on their variant.

Please help me understand as to how this will affect the performance.

Regads,

SuryaD.

[ThomasZloch]

A016 has the following primary key:

KAPPL, KSCHL, EVRTN, EVRTP

KAPPL is always 'M', but there are probably very many entries with 'M' (how many?) so the field is not selective.

Now depending on how S_KSCHL is filled you will see different response times, e.g. a single value should return quite quickly, whereas say 20 different values require much more data in A016 to be scanned. You can test yourself by tracing SE16 selections on A016 via tx ST05 (see blogs in sticky note to this forum).

If you look at the source code corrections done by SAP note 517989 in program SAPDBERM, you can see how SAP solved a similar performance problem, maybe you can apply a similar solution for your case.

Thomas

[Former Member]

Thomas thanks for responding to my query and thanks all for the inputs you have shared.

Regards,

SuryaD.