Skip to Content

Secondary index for Performance

Is there any restrictions or side effects on creating a secondary index on a date field ?.

Many of the reports use date in the selection criteria while searching on VBRK/VBRP or MKPF/MSEG tables. The execution takes long time as the data grows.

Is it recommened to create an index on date filed to improve the performance? say VBRK-FKDAT.

Thanks

Dev

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Best Answer
    Posted on Apr 30, 2012 at 08:02 AM

    For VBRK, VBRP tables we have a number of "index" tables that have FKDAT as part of the key. Maybe some of them may fit to your query.

    VRPMA - SD Index: Billing Items per Material

    VRKPA - Sales Index: Bills by Partner Functions

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Apr 30, 2012 at 12:28 PM

    Sukhdev Kaloor wrote:

    Is it recommened to create an index on date filed to improve the performance?

    Thanks

    Dev

    It depends ... 😉

    in general, if you query with EQUAL, a field is good for an index.

    For financial tables, when looking with LESS EQUAL THAN TODAY, it will rarely be benefitial, because this will be nearly ALL rows, while BIGGER EQUAL LAST MONTH might be very selective.

    I have an ODS Table in a BW system with 6 DATE fields and several indexes which have 2-4 of these fields to narrow down an active version of a document. I'd never go again without these :-)

    Volker

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on May 02, 2012 at 10:40 AM

    Hi Sukhdev,

    Secondary Indexes indeed improves the performance. But Lot of Secondary indexes will inturn will introduce load on the select query to identify the most appropriate index which may slow down the performance. In one of the discussion, I read that a table should not have more that 6 Secondary indexes. But in realily, in some cases, I have seen more than that :-) I hope this will help you

    Add a comment
    10|10000 characters needed characters exceeded

    • Be careful with generic statements, more indexes can improve or degrade performance, less indexes as well, all depending on circumstances. Too complex to describe in a few lines, there are articles and books about these topics. Whenever I will get a chance I will try to resurrect the FAQs and sticky threads of the "old SCN" ABAP performance forum.

      SAP standard often uses date fields in indexes, e.g. look at table BKPF where quick access by posting date or CPU date is required.

      In any case, before introducing new indexes (which take additional DB space and time per update operation, and might make matters more complex for the CBO), always try finding additional WHERE-conditions (e.g. LEDNR = "00" for CO tables or BSTAT = space for FI document tables) or alternative access paths to the desired data using existing tables that sort the data differently, as suggested by Yuri above.

      Your query must be constructed so that the CBO can make optimal use of existing primary or secondary indexes.

      Thomas

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.