topic Re: performance tunning in Application Development and Automation Discussions

performance tunning

Former Member — Mon, 22 Oct 2007 09:12:49 GMT

hi there,

i have coding part like this:

SELECT BUKRS BELNR GJAHR BUZEI BSCHL DMBTR WRBTR PSWSL ZUONR SGTXT KO HK KUNNR WERKS MENGE MEINS

FROM BS APPENDING TABLE itab1

FOR ALL ENTRIES IN itab

WHERE BUKRS = S_BUKRS

AND BELNR = T_BSIS-BELNR AND BUZEI = T_BSIS-BUZEI

AND KO IN ('JTH2003352', 'JTH2003353', 'JTH2003370',

'JTH2003371', 'JTH2003372', 'JTH5001320',

'JTH2000023', 'JTH2000024', 'JTH2000028')

AND HK IN ('0040000110', '0040000120').

when execute, it increase my runtime. how to solve this problem.

pls advise. thank you.

Re: performance tunning

Former Member — Mon, 22 Oct 2007 09:17:16 GMT

hi,

do like below

if itab[] is not initial.

SELECT BUKRS BELNR GJAHR BUZEI BSCHL DMBTR WRBTR PSWSL ZUONR SGTXT KO HK KUNNR WERKS MENGE MEINS

FROM BSET into TABLE itab1

FOR ALL ENTRIES IN itab

WHERE BUKRS IN S_BUKRS

AND BELNR = T_BSIS-BELNR AND BUZEI = T_BSIS-BUZEI

AND KO IN ('JTH2003352', 'JTH2003353', 'JTH2003370',

'JTH2003371', 'JTH2003372', 'JTH5001320',

'JTH2000023', 'JTH2000024', 'JTH2000028')

AND HK IN ('0040000110', '0040000120').

Endif.

Re: performance tunning

Former Member — Mon, 22 Oct 2007 09:18:43 GMT

AND BELNR = T_BSIS-BELNR AND BUZEI = T_BSIS-BUZEI

u are using for all entries in ITAB but in where clause u are using T_BSIS. I dont understand why... it should match.

Re: performance tunning

Former Member — Mon, 22 Oct 2007 09:28:06 GMT

sorry, the coding actually like this:

SELECT BUKRS BELNR GJAHR BUZEI BSCHL DMBTR WRBTR PSWSL ZUONR SGTXT KO HK KUNNR WERKS MENGE MEINS

FROM BSEG APPENDING TABLE itab1

FOR ALL ENTRIES IN T_BSIS

WHERE BUKRS = S_BUKRS

AND BELNR = T_BSIS-BELNR AND BUZEI = T_BSIS-BUZEI

AND KO IN ('JTH2003352', 'JTH2003353', 'JTH2003370',

'JTH2003371', 'JTH2003372', 'JTH5001320',

'JTH2000023', 'JTH2000024', 'JTH2000028')

AND HK IN ('0040000110', '0040000120').

but my T_BSIS is always have records and the amount is huge.

any other way to improve it?

pls advise. thank you.

Re: performance tunning

Former Member — Mon, 22 Oct 2007 13:19:20 GMT

1) Use the complete primary key BUKRS, BELNR, GJAHR, BUZEI of BSEG in the where clause. table BSIS has all these fields.

2) Delete adjacent duplicates to eliminate duplicate entries.

3) Do not use APPENDING TABLES when the table to which you are appending is empty. Check if the table contains any data. If so use APPENDING tables. Otherwise use INTO TABLES.

4) I am not sure what KO and HK are. They are not feilds on thatble BSEG.

Take a look at the code below.

DATA: t_bsis_tmp LIKE TABLE OF t_bsis.

IF NOT t_bsis[] IS INITAIL.

  t_bsis_tmp[] = t_bsis[].

  SORT t_bsis_tmp BY bukrs belnr gjahr buzei.

  DELETE ADJACENT DUPLICATES FROM t_bsis_tmp COMPARING
    bukrs
    belnr
    gjahr
    buzei.

  IF itab1[] IS INITIAL.

    SELECT bukrs
           belnr
           gjahr
           buzei
           bschl
           dmbtr
           wrbtr
           pswsl
           zuonr
           sgtxt
           ko
           hk
           kunnr
           werks
           menge
           meins
    FROM bseg
    INTO TABLE itab1
    FOR ALL ENTRIES IN t_bsis_tmp
    WHERE bukrs EQ s_bukrs
    AND   bukrs EQ t_bsis_tmp-bukrs
    AND   belnr EQ t_bsis_tmp-belnr
    AND   gjahr EQ t_bsis_tmp-gjahr
    AND   buzei EQ t_bsis_tmp-buzei
    AND   ko    IN ('JTH2003352', 'JTH2003353', 'JTH2003370',
                    'JTH2003371', 'JTH2003372', 'JTH5001320',
                    'JTH2000023', 'JTH2000024', 'JTH2000028')
    AND   hk    IN ('0040000110', '0040000120').

  ELSE.

    SELECT bukrs
           belnr
           gjahr
           buzei
           bschl
           dmbtr
           wrbtr
           pswsl
           zuonr
           sgtxt
           ko
           hk
           kunnr
           werks
           menge
           meins
    FROM bseg
    APPENDING TABLE itab1
    FOR ALL ENTRIES IN t_bsis_tmp
    WHERE bukrs EQ s_bukrs
    AND   bukrs EQ t_bsis_tmp-bukrs
    AND   belnr EQ t_bsis_tmp-belnr
    AND   gjahr EQ t_bsis_tmp-gjahr
    AND   buzei EQ t_bsis_tmp-buzei
    AND   ko    IN ('JTH2003352', 'JTH2003353', 'JTH2003370',
                    'JTH2003371', 'JTH2003372', 'JTH5001320',
                    'JTH2000023', 'JTH2000024', 'JTH2000028')
    AND   hk    IN ('0040000110', '0040000120').

  ENDIF.

Hope this helps.

Re: performance tunning

Former Member — Mon, 22 Oct 2007 13:28:15 GMT

hi, try this way

select BUKRS also from bsis into the internal table instead of putting s_bukrs

if t_bsis[] is not initial.

SELECT BUKRS BELNR GJAHR BUZEI BSCHL DMBTR WRBTR PSWSL ZUONR SGTXT KO HK KUNNR WERKS MENGE MEINS

FROM BSeg into TABLE itab1

FOR ALL ENTRIES IN t_bsis WHERE BUKRS = t_bsis-bukrs

AND BELNR = T_BSIS-BELNR AND BUZEI = T_BSIS-BUZEI

AND KO IN ('JTH2003352', 'JTH2003353', 'JTH2003370',

'JTH2003371', 'JTH2003372', 'JTH5001320',

'JTH2000023', 'JTH2000024', 'JTH2000028')

AND HK IN ('0040000110', '0040000120').

endif.

try to fetch maximum key fields from BSIS and provide it into BSEG in the above select.

Re: performance tunning

Former Member — Mon, 22 Oct 2007 13:49:07 GMT

It doesn't look too bad. You could try to add the fiscal year to the SELECT, but it won't help a lot. If you are going to SELECT a lot of data, it will take time.

You can also try adding the fiscal year and company code to your internal table:


CHECK NOT  t_bsis[] IS INITIAL.
SORT t_bsis BY bukrs belnr gjahr buzei.

SELECT bukrs belnr gjahr buzei bschl dmbtr wrbtr pswsl zuonr sgtxt ko hk
       kunnr werks menge meins
  FROM bseg APPENDING TABLE itab1
  FOR ALL ENTRIES IN t_bsis
  WHERE bukrs = t_bsis-bukrs
    AND belnr = t_bsis-belnr
    AND gjahr = t_bsis-gjahr
    AND buzei = t_bsis-buzei
    AND ko IN ('JTH2003352', 'JTH2003353', 'JTH2003370',
               'JTH2003371', 'JTH2003372', 'JTH5001320',
               'JTH2000023', 'JTH2000024', 'JTH2000028')
    AND hk IN ('0040000110', '0040000120').

Rob

Message was edited by:

Rob Burbank

Added SORT as well.

Message was edited by:

Rob Burbank

Re: performance tunning

Former Member — Mon, 22 Oct 2007 14:02:16 GMT

You can give only the key fields in the select statement and after having populated the itab1, filter the records.

SELECT BUKRS BELNR GJAHR BUZEI BSCHL DMBTR WRBTR PSWSL ZUONR SGTXT KO HK KUNNR WERKS MENGE MEINS

FROM BSEG APPENDING TABLE itab1

FOR ALL ENTRIES IN T_BSIS

WHERE BUKRS = S_BUKRS

AND BELNR = T_BSIS-BELNR

AND BUZEI = T_BSIS-BUZEI.

DELETE itab1 where not

( KO IN ('JTH2003352', 'JTH2003353', 'JTH2003370',

'JTH2003371', 'JTH2003372', 'JTH5001320',

'JTH2000023', 'JTH2000024', 'JTH2000028')

AND

HK IN ('0040000110', '0040000120') ).

This way, you would be using the primary index and will help in optimizing your select statement.

Regards

Navneet

Re: performance tunning

Former Member — Mon, 22 Oct 2007 20:36:52 GMT

Hi Simon,

Besides doing what the others recommended, make absolutely sure that there are no initial records in your "for all entries" table... i've found that to be the problem in some cases.