6 Replies Latest reply: Jul 7, 2008 1:19 PM by Thomas Zloch RSS

A017 goes via KAPOL and gives me a performance issue.

Martin Andersson
Currently Being Moderated

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

  • Re: A017 goes via KAPOL and gives me a performance issue.
    ilesh Nandaniya
    Currently Being Moderated

    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

  • Re: A017 goes via KAPOL and gives me a performance issue.
    ilesh Nandaniya
    Currently Being Moderated

    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

  • Re: A017 goes via KAPOL and gives me a performance issue.
    Thomas Zloch
    Currently Being Moderated

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

Actions