A016 table is giving performance issue

[former_member267445]

Hello Gurus,

I have a query on A016 table and get the condition record number. Below is the query

select kappl kschl evrtn evrtp dtabi datab knumh

into table t_a016 from a016

for all entries in t_ekab

where kappl = 'M'

    and kschl = 'PB00'

    and evrtn = t_ekab-ebeln

    and evrtp = t_ekab-ebelp

    and datbi >= t_ekab-bedat

    and datb  <= t_ekab-bedat.

from the above in t_ekab am having entries 30,000 records based on selection screen. I see that in A016 table there are 2lak entries. It is taking too much time(2hrs in background) retrieve the data from A016 table.

Did I miss anything in the select query and how can I make above select query to fetch effective and faster?

Please help me to fine tuning of the above query.

Thanks and Regards,

Mualikrishna Peravali

[Former Member]

Hi Murali,

Please use Inner Join in the select statement instead of For all entries and you can check how much time it is taking in ST05 , when you replace with Join.

I think the performance will improve and generally i prefer to use Join .

also check the below links.

http://scn.sap.com/thread/3370614

http://scn.sap.com/thread/919187

[former_member267445]

Hello Ramya,

Thank you for response. Actually I will also prefer to use join, but here before this select statement I have done some calculation part on t_ekab for filtering the records and then I have written the select statement on A016.

Thanks and Regards,

Muralikrishna Peravali.

[Former Member]

This SELECT seems OK to me. Are you sure the problem isn't with the SELECT from EKAB?

Rob

[former_member267445]

Hello Rob,

No, there is no problem with EKAB selection. The problem is at query on A016.

Thanks and Regards,

Muralikrishna Peravali

[Former Member]

Hi Murali,

Can you try creating by creating a secondary Index for all the key fields and also include DATA.

Make sure that the sequence of the fields in the select query matches the sequence mentioned in the Secondary-Index.

Hope there will be improvement in performance.

Is 2 hrs is consumed only on fetching the records or it includes other data processing also ?

Regards,

Gupta

[former_member267445]

Hello Gopal,

Thank you for the reply. As am aware that we can not create a secondary index on table AXXX because those are pooled tables. Correct me if am wrong?

2 hrs is only fetching the data from the table A016.

Thanks and Regards,

Muralikrishna Peravali

[Former Member]

It doesn't really matter because you are already using the primary key.

Rob

[Former Member]

Hi Murali,

Table A016 is a transparent table so you can create it easily.

Also since you are using for all entries do the following in internal table t_ekab before calling the select query.

i) Check if the itab t_ekab is not initial or else the select query on A016 will return all the entries

iI) Sort the itab t_ekab

iiI) Delete duplicate entries comparing the fields that you are using in query condition.

Regards,

Gupta

[Former Member]

Hi Rob,

I suggested to create a secondary index because in the condition "datab" is a non-key field.

Regards,

Gupta

[Former Member]

Interesting - I see it as a pooled table. What database system are you on?

Rob

[Former Member]

But the primary key already uniquely identifies all records, so adding another field couldn't help.

Rob

[former_member267445]

Gopal,

I too see A016 as pooled table.

Thanks and Regards,

Muralikrishna Peravali

[Former Member]

Database is Hana-DB.

If the table is Pooled table then secondary index wont work.

[Former Member]

Hi Murali,

Since its a pooled table, secondary index wont work now.

Step 1. Reducing the internal table entries:-

As already suggested, first do the below set of changes.

Then try with the following changes:-

i) Check if the itab t_ekab is not initial or else the select query on A016 will return all the entries

iI) Sort the itab t_ekab

iiI) Delete duplicate entries comparing the fields that you are using in query condition.

Step 2. Using parallel Process:-

I am aware of what and all you are doing after fetching the data from A016. But if you are doing some logic buildings after DB selection and there is no dependency among the records then you can opt the below steps:-

i) Depending on the number of  records in T_EKAB, divide into multiple into smaller chunks

ii) Create a FM which includes the Data selection from A016 and also the further processing

iii) For every smaller chunks call the FM in background task.

Hope by doing so there will be increase in performance.

Regards,

Gupta

[former_member267445]

Hello All,

I am closing this thread. Actually in the select query I forgot include full primary key and was searching for performance. I forgot to include KSCHL in where clause that is why it was taking time. I have included that and now the report is in production last week.

Apologies for the inconvenience.

Thanks and Regards,

Muralikrishna.

[Former Member]

But KSCHL was in the WHERE clause that you posted.

Rob

[former_member267445]

Hello Rob,

Yes I have posted but not implemented in select query. I was thinking KSCHL is there in WHERE clause.

Thanks and Regards,

Muralikrishna