Performance problem with DFKKOP even after using Index fields

[Former Member]

Hello Everyone,

I am using the fields VKONT, BUKRS, BUDAT to hit DFKKOP.

I tried using PACKAGE SIZE method and PACKAGE SIZE along with OPEN CURSOR method. Also I verified in ST05 that the query is making use of an index designed for "BUKRS and BUDAT".

However, the query in all cases is taking a huge amount of time and the report never gets past this statement.

    OPEN CURSOR WITH HOLD cursor FOR SELECT vkont hvorg tvorg budat FROM dfkkop

                                     FOR ALL ENTRIES IN it_unqca

                                     WHERE bukrs = c_ui01

                                     AND vkont = it_unqca-vkont

                                     AND budat BETWEEN v_6mstartdate AND sy-datum.

    DO.

      FETCH NEXT CURSOR cursor APPENDING TABLE it_lp PACKAGE SIZE c_5000.

      IF sy-subrc NE 0.

        EXIT.

      ENDIF.

      CALL FUNCTION 'DB_COMMIT'.

    ENDDO.

    CLOSE CURSOR cursor.

Could anyone suggest a way to overcome this performance issue? Any suggestion will be helpful.

I searched SDN and nothing seems to satisfy this question. ( Performance issue after using index)

Thanks,

Sam

[Former Member]

Hi Sam

Could yuo please be more specific on the index the execution plan is using? For example which fields it contains.

I can't see a SAP standard index suitable for SQL above. Also consider BUKRS is not a very "selectable" value, means often there's only one or two different values in the table.

Looking into the indices delviered by SAP, I consider DFKKOP~4 the best one for SQL above. However, you would have to add the field AUGST to your SQL above:

AND AUGST IN (' ','9')

Personally, I avoid the statement:

FOR ALL ENTRIES

For performance reasons and I'm surprised to see it in an OPEN CURSOR (actually, it doesn't make much sense there, because FOR ALL ENTRIES executes the SQL for each 5 entries of the internal table).

Are you certain you need this?

How many entries do you expect in the internal table IT_UNQCA?

Is it possible to JOIN the SQL statement populating IT_INQCA with the SQL statement of the OPEN CURSOR?

Could you have an internal range table instead?

Yep

Jürgen

[Former Member]

Hello Juergan,

The only fields I could use for selection are VKONT, HVORG, TVORG, BUKRS and BUDAT.

I tried using all of them but in vain, and the index it right now uses ( with fields BUKRS VKONT and BUDAT in selection) during execution is a Z index which has the fields BUKRS and BUDAT.

I tried without using OPEN CURSOR as well but no use. I cannot use a join because IT_unqca is already fetched using a select with join on ( it will have around 300,000 records) and joining DFKKOP with them will I think overload it.

What would be your suggestion in using a select within loop where i loop VKONT , i do not it causes unnecessary DB hit, but couldn't think of any other solution.

[Former Member]

Doesn't DFKKOP~4 need clearing date as well?

[Former Member]

Hi Sam

The most important part for using the correct index are not the fields you select from, but the fields in the where-condition, in your case BUKRS, VKONT and BUDAT (which is not the clearing date, but the posting date).

It's not required to use all fields of an index, but the fields in the order of the index without "gaps" have to be used. See index DFKKOP~4 it contains following fields in given order:

MANDT

AUGST

VKONT

BUKRS

AUGDT

The SAP/Database interface automatically adds the MANDT, so ignore the field. The fields you already use in your where-condition are VKONT and BUKRS. Now the issue is the field AUGST. If we wouldn't know the possible values for the field AUGST we would have to create our own index w/o AUGST. However the values for AUGST is either ' ' for open items or '9' for cleared ones. So with a small change in the code we can use an existing index.

The additional fields at the end of the index are "ignored", here AUGDT. Now in case you would have a very large number of documents per contract account (VKONT) you might want to include the posting date BUDAT, which means you would have to create your own index.

Additional custom indices on tables might improve your selection, but impact the timings of table changes (insert/update). If it's easy to use an existing index, do so.

Looking back into your code, I think the FOR ALL ENTRIES is the bigger problem to solve.

Yep

Jürgen

[Former Member]

Hello Juergen,

I was able to find an alter way for "for all entries".

Thanks for your inputs.