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

Performence issue on Select statement

Hi all,

BKPF is very huge table in production server and its mandatory to retrive records. there is no suible secondary idexes also.

how to overcome this problem

SELECT bukrs belnr gjahr blart bldat budat monat

usnam xblnr waers awsys xref1_hd xref2_hd

FROM bkpf

INTO TABLE i_bkpf

WHERE bukrs IN s_bukrs AND

gjahr IN s_gjahr AND

blart IN s_blart AND

bldat IN s_bldat AND

budat IN s_budat AND

monat IN s_monat AND

usnam IN s_usnam AND

awsys EQ c_zgiqadkob.

all suggestions are welcome

Regards, Sri harsha

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Jan 21, 2008 at 08:52 AM

    Instead of this try to fetch data from

    BSIK & BSAK for accounts payable and

    BSID and BSAD for accounts receivable scenarios.

    after this select fetch the required data from BKPF using the BELNR retrieved from previous select

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 21, 2008 at 09:02 AM

    Hi,

    try including "AND BSTAT = space" in your select statement, there are secondary indexes on BLDAT and BUDAT that include this field. Then hope that the optimizer finds the best access path...

    But you need to have a narrow selection on either BLDAT or BUDAT for this to work.

    Cheers

    Thomas

    Add a comment
    10|10000 characters needed characters exceeded

    • >

      > if i include remaining parameter along with select - index statement, will it show improvement in perfomence

      I am not sure what you mean here, please elaborate.

      Fill as many selections fields as possible (especially BUKRS, but also BLART and/or BUDAT, BLDAT) to narrow down the result set, the database optimizer should then choose the correct index for maximum performance.

      Usually BKPF is large though, so even when using the best access path there will always be some runtime and database load.

      Greetings

      Thomas

  • author's profile photo Former Member
    Former Member
    Posted on Jan 21, 2008 at 02:50 PM

    Please see:

    [Using an Index When You Don't Have all of the Fields|/people/rob.burbank/blog/2006/09/13/using-an-index-when-you-dont-have-all-of-the-fields]

    Rob

    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.