A017 goes via KAPOL and gives me a performance issue.

[Former Member]

Hi,

I have a query (I need to figure out werks based on a price condition ) :

select single werks from a017

into (l_werks)

where kappl = 'M'

and kschl = 'PB00'

and knumh = change_document_header-objectid.

The problem with this query is that it makes a full table scan. About 300 000 records.

I don't have any more data to add to my query.

A017 is a pooled table.

When I run this query in ST05 I can see that the query access a table name KAPOL which is a Table pool.

So... the select is on A017 and is accessed via KAPOL.

Any Pointers ?

Br,

Martin

[former_member387317]

Hi Martin Andersson,

Try to specify some more fields in where condition if possible as u r using select single u must specify the exact key combination so that it will fetch that particular record u need and it will improve performance as well...

below are key field of table A017

KAPPL

KSCHL

LIFNR

MATNR

EKORG

WERKS

ESOKZ

DATBI

And u r using change_document_header-objectid in where condition so check weather it is initial or not befor your select statement...

if change_document_header-objectid is not initial.

select single werks from a017
into (l_werks)

where kappl = 'M'
and kschl = 'PB00'
and knumh = change_document_header-objectid.

endif.

or use for all entries if multiple record are present for ur key combination...

if change_document_header[] is not initial.

select single werks from a017
into table itab

for all entries in change_document_header

where kappl = 'M'
and kschl = 'PB00'
and knumh = change_document_header-objectid.

endif.

Also refer below threads...

Hope it will solve your problem..

Thanks & Regards

ilesh 24x7

[Former Member]

Hi,

I don't have any more fields to put into Where Clause and the knumh is unique.... so FAE will not help.

What I belive I need is to put an index on A017-knumh or find the transp.table for this...

More Pointers ?

Br,

Martin

[former_member387317]

Hi Martin Andersson,

As A017 is pooled table U can't create secondary INDEX on it.

So either provide full key combination in where clause

or change the way u r selecting data through diff tables...

try to find some other tables where u can find the filelds which u can provide in where condition of select statement..

u can ask enduser to put some restriction on selection-screen to provide some more fileds for technical feasibility...

Try to find alternate tables for it..

U can't create secondary INDEX on pooled and cluster table.

If u open the table in se11 in change mode the push button Indexs will be disabled. So u can't do that.

This is because for many tables in Data dictionary there will be only one table in data base for pooled or cluster tables called as table pool/Table cluster. Secondary index will be based on fields specified in Index for one table. So if u create Index for one pooled/cluster table the same fields may or may not be there in other tables in table pool/Cluster. So there will be inconsistency occurs for the data base optimizer when u write a select query. That is why it is not possible to create secondary Index for pooled and cluster table.

In case of Transaparent tables it will be one to one relationship i.e One table in DD and one table in Data base. Also field names and table name in DD and data base is same in case of transaparent table but is different in case of pooled and cluster tbale.

Hope it will solve your problem..

Thanks & Regards

ilesh 24x7

[ThomasZloch]

Hi,

selecting from A017 with just KAPPL and KSCHL is not selective, thus the full table scan (KNUMH is not part of the primary key).

Since you have the value of KNUMH, you can read table KONH and get the value of VAKEY. This VAKEY contains all the keyfields for A017 (LIFNR, MATNR, EKORG, WERKS, ESOKZ) in a concatenated form. You could use these values for the A017 access, this should speed it up considerably.

Greetings

Thomas

P.S. maybe you don't need to access A017 at all, since WERKS is already part of KONH-VAKEY...

[Former Member]

Hi,

This is perfect!!!

Is there a FM or something to use in order to grab these values out of key...or do I have to use mywerks = l_key+33(4)

Br,

Martin

[ThomasZloch]

You can use l_key+32(4) or more elegantly define a workarea with the fields LIFNR MATNR EKORG WERKS and move the content of VAKEY there to pick up <wa>-WERKS.

Thomas