topic Re: A016 table is giving performance issue in Application Development and Automation Discussions

A016 table is giving performance issue

former_member267445 — Tue, 07 Apr 2015 11:31:35 GMT

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

Re: A016 table is giving performance issue

Former Member — Tue, 07 Apr 2015 13:25:48 GMT

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

Re: A016 table is giving performance issue

Former Member — Tue, 07 Apr 2015 13:35:30 GMT

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

Rob

Re: A016 table is giving performance issue

Former Member — Tue, 07 Apr 2015 13:38:14 GMT

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

Re: A016 table is giving performance issue

former_member267445 — Tue, 07 Apr 2015 13:52:18 GMT

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.

Re: A016 table is giving performance issue

former_member267445 — Tue, 07 Apr 2015 13:53:29 GMT

Hello Rob,

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

Thanks and Regards,

Muralikrishna Peravali

Re: A016 table is giving performance issue

former_member267445 — Tue, 07 Apr 2015 13:55:48 GMT

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

Re: A016 table is giving performance issue

Former Member — Tue, 07 Apr 2015 13:59:51 GMT

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

Rob

Re: A016 table is giving performance issue

Former Member — Tue, 07 Apr 2015 14:03:45 GMT

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

Re: A016 table is giving performance issue

Former Member — Tue, 07 Apr 2015 14:07:08 GMT

Hi Rob,

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

Regards,

Gupta

Re: A016 table is giving performance issue

Former Member — Tue, 07 Apr 2015 14:09:38 GMT

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

Rob

Re: A016 table is giving performance issue

Former Member — Tue, 07 Apr 2015 14:11:14 GMT

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

Rob

Re: A016 table is giving performance issue

former_member267445 — Tue, 07 Apr 2015 14:22:08 GMT

Gopal,

I too see A016 as pooled table.

Thanks and Regards,

Muralikrishna Peravali

Re: A016 table is giving performance issue

Former Member — Tue, 07 Apr 2015 14:24:02 GMT

Database is Hana-DB.

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

Re: A016 table is giving performance issue

Former Member — Tue, 07 Apr 2015 14:42:25 GMT

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

Re: A016 table is giving performance issue

former_member267445 — Wed, 15 Apr 2015 13:54:50 GMT

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.

Re: A016 table is giving performance issue

Former Member — Wed, 15 Apr 2015 14:00:41 GMT

But KSCHL was in the WHERE clause that you posted.

Rob

Re: A016 table is giving performance issue

former_member267445 — Wed, 15 Apr 2015 14:13:21 GMT

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