topic Re: ABAP Query Code timeout issue in Application Development and Automation Discussions

ABAP Query Code timeout issue

former_member355038 — Wed, 06 Aug 2014 09:04:54 GMT

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

Re: ABAP Query Code timeout issue

Former Member — Wed, 06 Aug 2014 09:09:10 GMT

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

Re: ABAP Query Code timeout issue

former_member355038 — Wed, 06 Aug 2014 10:06:36 GMT

Hi Nico

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

thanks

Joe

Re: ABAP Query Code timeout issue

former_member355038 — Wed, 06 Aug 2014 10:14:57 GMT

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

Re: ABAP Query Code timeout issue

former_member202771 — Wed, 06 Aug 2014 10:34:46 GMT

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'

KSTAR = '0005767012'

and

OBJNR = PRPS-OBJNR

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

Thanks,

Anil

Re: ABAP Query Code timeout issue

Former Member — Wed, 06 Aug 2014 10:49:13 GMT

Hello Joe,

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

next to the technical table settings.

Regards Nico

Re: ABAP Query Code timeout issue

Former Member — Wed, 06 Aug 2014 11:10:02 GMT

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