Skip to Content
author's profile photo Former Member
Former Member

A017 goes via KAPOL and gives me a performance issue.

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Best Answer
    Posted on Jul 07, 2008 at 09:10 AM

    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...

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 07, 2008 at 05:44 AM

    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...

    a017-performance-problem

    alternate-to-a017-table

    problem-with-a017-table

    Hope it will solve your problem..

    Thanks & Regards

    ilesh 24x7

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

  • author's profile photo Former Member
    Former Member
    Posted on Jul 07, 2008 at 09:03 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.