Performance issue AUFK Table?

[Former Member]

Hello all,

in database table AUFK, i have craeted a include with 2 fields:

zzebeln , zzebelp >>purchase order no, and item no.

i have used used these fields in CO01.

my problem is when am fetching data by entereing these P.O. no and item no...

the query takes unlimitede amount of time and eventually the transaction

exits...timeout problem.

here is my query:

select aufk~aufnr aufk~auart
      aufk~ERDAT
      aufk~werks aufk~zzebeln
      aufk~zzebelp
      afko~gamng"prod qty
      from aufk
      INNER JOIN afko
      on aufk~aufnr eq afko~aufnr
      into TABLE i_aufk
    FOR ALL ENTRIES IN i_ekpo "CONTAINS ebeln and ebelp from ekpo
        where zzebeln eq i_ekpo-ebeln
      AND zzebelp eq i_ekpo-ebelp
*      and werks eq i_ekpo-werks
      and bukrs eq '1000'
      and autyp eq '10'
*      *  secandary index used.
        and kokrs eq '1000'
  and auart eq 'YBM3'
  and ABKRS eq Space.

as these are manually created fields...these is not present any index....

please suggest me how to extract data (PROD. ORDER NO. From AUFK)

by using ZZEBELN ZZEBELP.

[krishna_k19]

Hi ,

  Did you checked , whether PO num, another fileds are updating or not in AUFK table.

Regards,

Krishna

[Former Member]

of-course, the values for zzebeln  and zzebelp are updating in the table.

[former_member386202]

Hi,

I think you will have to create secondary index on EBELN and EBELP.

Regards,

Prashant

[former_member195402]

Hi,

if you want to access AUFK by the fields ZZEBELN and ZZEBELP then you have to create a new Z-index.for them (for example index ZEB with fields MANDT,  ZZEBELN and ZZEBELP).

I would also suggest to set the init flags for both fields in AUFK.

Regards,

Klaus

[Former Member]

klaus,

i have already created a z index in the aufk table.

have a look,

but this hardly helps,

kindly guide me how to use this

newly created index ZEI.. in the select query.

I would also suggest to set the init flags for both fields in AUFK.

Please Shed some light >> What do you mean by that?

[Former Member]

Hi,

   r u sorted table i_ekpo before calling the select query.

try like below code

SELECT * FROM spfli

  INTO TABLE t_spfli

  %_HINTS ORACLE 'INDEX("<tablename>" "<tablename>~<Index Identifier>")'.

Message was edited by: Vinod Kumar A

[Former Member]

Hi abhishek,

Index does not exist in database system.

Use DB02 to create index in database.

http://help.sap.com/saphelp_nw70/helpdata/EN/f2/31ad7d810c11d288ec0000e8200722/content.htm

Regards,

Mordhwaj

[former_member386202]

Hi,

You will have to add all the fields which are there in select query where condition into your created secondary index ZEI and in same sequence.

1.   where zzebeln eq i_ekpo-ebeln 
2.       AND zzebelp eq i_ekpo-ebelp 
3. *      and werks eq i_ekpo-werks 
4.       and bukrs eq '1000' 
5.       and autyp eq '10' 
6. *      *  secandary index used. 
7.         and kokrs eq '1000' 
8.   and auart eq 'YBM3' 
9.   and ABKRS eq Space. 

Regards,

Prashant

[ThomasZloch]

Not correct, ZZEBELN is very selective so sufficient for this index, order of fields in WHERE-clause and in index need not match to make the index be found by the optimizer.

Thomas

[former_member195402]

Hi,

you have created an index, but it seeems, that it still doesn't exist on the database. That's why it doesn't work yet. Go to the index overview, right-click the new index and try option CREATE.

The init flags can be set in SE11 for structure CI_AUFK with Extras-> DB Attributes -> Initialization Flag On/Off. If they are set to on, you cold it see in AUFK (look at your 1st picture 3rd column).

Regards,

Klaus

[Former Member]

Hi,

I think that you should test that into  i_ekpo there is at least one record and then you can try to create a secondary index on ebeln and ebelp.

Regards

Ivan Marconato

[Former Member]

ivan,

i have made sure that i_ekpo is filled with data.

moreover, before the select query starts i have placed a condition.

If i_ekpo[] is not initilal.
"select query
endif.

[ThomasZloch]

Your screenshot shows that the index is not created yet on the database. Create it via SE14 and try again.

You might not need ZZEBELP in the index, as ZZEBELN should be very selective already. Saves a little space...

Another suggestion: change the description of the index so that it briefly explains why it was created, e.g. for which process.

Thomas

[SuhaSaha]

change the description of the index so that it briefly explains why it was created, e.g. for which process.

That's what a quality response should look like!

[Former Member]

Hi Abishek,

You had created 2 custom includes in your DB Table. But there are non primary key fields.

Try to build the secondary index to the fields which you are using in the select query to

fetch the data from the table.It will increase the performance.

Let me know if any clarification/help required.

Thanks & Best Regards.

Pavan Neerukonda.

[Former Member]

now i have created index and it also exists in database DB6.

but still it is taking long time.

if i_ekpo containes 500 entries, then extracting

data from AUFK..takes longer than 5 minutes.

now what could optimize more?

[Former Member]

Hi Abishek,

After building the secondary index how you are writing the select query to fetch the data will you paste that code here.

For Ex:

SELECT carrid connid cityfrom FROM spfli INTO (xcarrid, xconnid, xcityfrom) WHERE carrid = 'LH ' AND cityfrom = 'FRANKFURT' %_HINTS ORACLE 'INDEX("SPFLI" "SPFLI~001")'. WRITE: / xcarrid, xconnid, xcityfrom. ENDSELECT. 

Thanks & Regards.

Pavan.N

[former_member195402]

In our database system Oracle a new index will not be taken from system automatically in all cases. Sometimes a new database statistic run has to be done, if the last statistic run is too old.

Please ask your basis team to start a database statistic run in your development system for table AUFK.

Regards,

Klaus

[Former Member]

i am using the same select query..as i have mentioned in my question..see my very first post.

i thought that abap/sql optimizer used the index automatically..whatever it suits.

[Former Member]

klaus,

firstly, we have DB6 as our database system.

currently, i am the only guy as abap team and as basis team as well.

can u please shed some light on how we can database statistic run in your development system for table AUFK.?

[raymond_giuseppi]

Could you execute a SQL trace to analyze how the system performs your selection, does it use you newly created index, use explain option.

(Of course I suppose you alerady checked that your internal table i_ekpo was not empty )

Regards,

Raymond

[Former Member]

raymond,

i have checked that there is alteast one record in the table

before using for all entries in the select statement.

while processing this AUFK >> select query.

i have activated the trace for this program only.

could u please guide me where in trace i can deduct

then which index has been used.

have a look at the screenshot of the trace.

this trace is only for that single select query.

[former_member193464]

As Raymond said there is explain button just put your cursor on the query and click explain , it will show you if it is using the index created by you or not.

[raymond_giuseppi]

Yes click on the explain button, to see which indexes were checked and the one chosen.

[Former Member]

as stated by you, i have placed cursor on one query, and went to EXPLAIN.

there were lots of menu options, i was wondering which would show me the index used!

hope i am looking into the right direction.

if yes...then there are no logs for the query.

i have marked the option on left hand side.

[ThomasZloch]

You ended up in the wrong place, somehow. Please have a look here for some background information:

Thomas

[former_member193464]

can you show the screenshot when you double click on the select query where some ebeln value has been passed.

[raymond_giuseppi]

Try to display execution plan, should look like

Click on an OPEN statement, then on Explain...

Regards,

Raymond

[Former Member]

Dear abi,

Fetch data based on the EBLEN and EBELP from AUFK.

Filtration for bukrs, autype, kokts, auart, abkrs do it after the select query.

Thanks & Regards,

buz_sap