topic Re: Performance issue AUFK Table? in Application Development and Automation Discussions

Performance issue AUFK Table?

Former Member — Thu, 13 Jun 2013 11:53:47 GMT

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.

Re: Performance issue AUFK Table?

krishna_k19 — Thu, 13 Jun 2013 11:57:48 GMT

Hi ,

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

Regards,

Krishna

Re: Performance issue AUFK Table?

former_member386202 — Thu, 13 Jun 2013 11:59:01 GMT

Hi,

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

Regards,

Prashant

Re: Performance issue AUFK Table?

Former Member — Thu, 13 Jun 2013 12:00:08 GMT

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

Re: Performance issue AUFK Table?

Former Member — Thu, 13 Jun 2013 12:02:58 GMT

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

Re: Performance issue AUFK Table?

Former Member — Thu, 13 Jun 2013 12:06:59 GMT

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

Re: Performance issue AUFK Table?

Former Member — Thu, 13 Jun 2013 12:08:27 GMT

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?

Re: Performance issue AUFK Table?

Former Member — Thu, 13 Jun 2013 12:14:11 GMT

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.

Re: Performance issue AUFK Table?

ThomasZloch — Thu, 13 Jun 2013 12:28:02 GMT

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

Re: Performance issue AUFK Table?

Former Member — Thu, 13 Jun 2013 12:40:47 GMT

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

Re: Performance issue AUFK Table?

Former Member — Thu, 13 Jun 2013 12:53:39 GMT

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

Re: Performance issue AUFK Table?

former_member386202 — Thu, 13 Jun 2013 13:02:10 GMT

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.

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.

Regards,

Prashant

Re: Performance issue AUFK Table?

ThomasZloch — Thu, 13 Jun 2013 13:37:08 GMT

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

Re: Performance issue AUFK Table?

Former Member — Fri, 14 Jun 2013 04:09:02 GMT

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

Re: Performance issue AUFK Table?

Former Member — Fri, 14 Jun 2013 04:23:17 GMT

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.

Re: Performance issue AUFK Table?

SuhaSaha — Fri, 14 Jun 2013 05:39:21 GMT

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!

Re: Performance issue AUFK Table?

Former Member — Fri, 14 Jun 2013 06:24:05 GMT

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?

Re: Performance issue AUFK Table?

Former Member — Fri, 14 Jun 2013 06:33:48 GMT

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

Re: Performance issue AUFK Table?

Former Member — Fri, 14 Jun 2013 06:43:00 GMT

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

Re: Performance issue AUFK Table?

Former Member — Fri, 14 Jun 2013 06:43:28 GMT

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.