All entries in - II

[former_member227596]

I have two tables APFO-Production Data & COEP - Costing Data. Both table have no common data to match.

1) First I replace OBJNP field which is blank replace data with 'OR'+AUFNR using concatenate in internal table.

2) Now AFPO-OBJNP & COEP-OBJNR can make link.

3) I use for all entries in to get data from COEP where OBJnr = AFPO-OBJNP.

When I run this on Development server it working & takes data for month in less than 3 minutes.

When I run same report on Production Server, It shows error Excceds Time Limit.

That I donnot now. There r 4 Lakhs records in COEP table.

My aim is to run report fast. Pl. help.

regards

Vikas

[Former Member]

Hi,

It causes slow bec OBJNR not a key-field.

Eventhoug secondary index is created for field OBJNR but it has dicalred as 3rd field in the sequence of seconday index.

If you really want to increase perforamnce you need to create one more secondary index on OBJNR filed, but i am not sure your client people give permission to create secondary index, if then agree to create index you can create it and definetly you will reach you requirement ie fast access data.

here i am giving the importance of secondary indexes and how to create index.

Hi,

Normally key-fields are used to unique identification.

standard or z tables create the index by default for key-fields.

for better perfomance of select statements we have to use the key fileds in where condition.

Suppose there is a situation example you need to get all material information from MARA table by using material group is MATKL, MBRSH . but these fields are non-key fields, when we use in select statement performance wise it causes worst.

in this situation you have to create secondary index, but remember one thing dont create secondary index unless it highly priority to create. its take same memory capcity of what exactly table having, lets suppose mara occupies memory like 10 MB, when u create secondary index it will also require 10 MB memory.

for this also you need to take permission from basis people.

Steps to create Secondary index.

-goto SE11 select the table name and dispay it.

- press the tab '<b>INDEX'</b> then you will get one popup screen here you have to give name of the index and press ok it ask work bench request under that save that.

-it opend the index maintain screen and here you need to fill some attributes like 'short description', non-unique or unique index.

-enter the field names which you want to create index and press enter and save it.

-<b>activate it</b>.

<b>Reward with points if useful.</b>

Regards,

Vijay

[Former Member]

Vikas,

See my answer to your other post on this question.

You need to have LEDNR in the where block of your select so it uses the index and does not do a table scan. Table scan in DEV will be quick because of small table size so no problem. Table scan in production will take forever - and will be repeated for each entry in "FOR ALL ENTRIES"

Andrew

[former_member227596]

Hi Vijay,

MANDT Client cLNT 3

OBJNR Object number CHAR 22

KSTAR Cost Element CHAR 10

GJAHR Fiscal Year NUMC 4

PERIO Period NUMC 3

PAROB1 Partner object (always filled) CHAR 22

Following fields are defined in index 2 in the table COEP.

SAP defined standard index with field OBJNR.

regards

Vikas

Message was edited by:

Vikas Puri

[Former Member]

While it is true index 2 on this table is defined to use OBJNR without specifying other fields, this index is unfortunately delivered by SAP as "Not created in Database" and it therefore cannot be used without activation and generation. Doing so could have a significant impact on performance if the table is large - that is why it is delivered inactive.

If you want to turn it on (or turn on index 4 which is also delivered inactive) you should check SAP notes for advice first.

Otherwise you will need to use index 1 to efficiently search for OBJNR and this requires field LEDNR to be specified. Try:

SELECT ....  "List desired fields including ALL key fields of table
   INTO TABLE GT_COEP
   FROM COEP
   FOR ALL ENTRIES IN GT_AFPO
   WHERE LEDNR = '00' 
   AND OBJNR = GT_AFPO-OBJNP.

Andrew

[santosh_patil6]

hi,

Using seconday index should be the solution. I also recommend you to sort the internal table(used in 'for all entries') by fields that will be used in the where clause of the select statement.

This will add to the performance imporvement.

Regards,

Santosh