ABAP Query Code timeout issue

[former_member355038]

Hi,

I have a SAP query which was the following code which is causing a timeout issue when running the report, can anyone suggest how I can change the code to make it more efficent?

* This gets the sum of all labour costs on WBSEs.
  clear X_COEP_WKGBTR_LAB_W.
  if PRPS-OBJNR is not initial.
  select sum( WKGBTR ) from COEP
    into X_COEP_WKGBTR_LAB_W
    where
    PERIO = ZPERIODT
    and
    GJAHR = ZYEAR
    and
    KSTAR >= 'A43'
    and
    KSTAR =< 'A43ZZZZZZZ'
    OR
     KSTAR = '0005767012'
    and
    OBJNR = PRPS-OBJNR.
  endif.

thanks

Joe

[Former Member]

Hello,

try:

  select sum( WKGBTR ) from COEP
    into X_COEP_WKGBTR_LAB_W
    where ( PERIO = ZPERIODT
    and    GJAHR = ZYEAR
    and KSTAR >= 'A43'
    and KSTAR =< 'A43ZZZZZZZ' )
    OR
    ( KSTAR = '0005767012'
    and
    OBJNR = PRPS-OBJNR ).

And be sureyou have an created an table index on the field KSTAR, because this one is used in both where-parts.

otherwise hes doing a full table scan.

Best regards,

Nico

[former_member355038]

Hi Nico

Thanks for help, how do I create a table index on the KSTAR field in the query?

thanks

Joe

[former_member355038]

Nico,

This is another field on the query which I think maybe need to be changed to be more efficient, what do you think?

* This gets the CYTD sum of all material costs on NWAs.
clear X_COEP_CYTD_MAT_N.
if AFVC-OBJNR is not initial.
select sum( WKGBTR ) from COEP
  into X_COEP_CYTD_MAT_N
  where
  PERIO =< ZPERIODT
  and
  GJAHR = ZYEAR
  and KSTAR >= '0000500000'
  and KSTAR =< '0009999999'
  and KSTAR <> '0005770000'
  and KSTAR <> '0005720000'
  and KSTAR <> '0005720020'
  and KSTAR <> '0005767012'
  and
  OBJNR = AFVC-OBJNR.
endif.

thanks

Joe

[Former Member]

Hello Joe,

the index can be created from transaction se11 on the top menu,

next to the technical table settings.

Regards Nico

[former_member202771]

Hi Joe,

I see none of the Key fields were used in select query.

Create a secondary index for the table and use it in select query.

It increases the performance.

COEP~1 and COEP~2 are already existing.

select sum( WKGBTR ) from COEP

    into X_COEP_WKGBTR_LAB_W

    where

    PERIO = ZPERIODT

    and

    GJAHR = ZYEAR

    and

    KSTAR >= 'A43'

    and

    KSTAR =< 'A43ZZZZZZZ'

    OR

     KSTAR = '0005767012'

    and

    OBJNR = PRPS-OBJNR

%_HINTS ORACLE 'INDEX("COEP" "COEP~1").

Thanks,

Anil

[Former Member]

Hi Joe,

Can you try couple of things to check if the performance improves..

1. try SQL trace which will tell you which index it is using.. as I understand your select query does not have the key fields in where clause so apart from PERIO it will basically do a sequential scan.

2.check if you can or have an alternate index on this table and if the Select is accessing the same.

3. get the data in internal table and then sum the WKGBTR, possibly that will be much faster.

Hope this helps.

Regards