topic Performance in BSIS in Application Development and Automation Discussions

Performance in BSIS

Former Member — Wed, 17 Oct 2007 05:41:31 GMT

Hi gurus,

Need your help. I have more than 10,000 entries in BSIS and it took me 5mins to retireve it. Here's my code...


  SELECT a~mandt
         a~belnr
         A~BUZEI
         a~mwskz
         a~budat
         A~SHKZG
         A~DMBTR
         b~lifnr
         B~NAME1
         b~ktokk
  APPENDING CORRESPONDING
  FIELDS OF TABLE lt_tab
  FROM bsak AS a
  INNER JOIN lfa1 AS b
  ON a~lifnr = b~lifnr
  WHERE a~bukrs EQ so_ccode
  AND A~GJAHR EQ LV_YEAR
  AND a~budat IN so_postp
  AND MWSKZ IN SO_TXCDE.

  SORT lt_tab1 BY lifnr belnr.
  DELETE LT_TAB WHERE KTOKK EQ 'UEMP'.

  if not lt_tab[] is initial.
    SELECT BELNR
           MWSKZ
           SHKZG
           DMBTR
           WAERS
           HKONT
    INTO CORRESPONDING FIELDS OF TABLE LT_TAB2_TEMP
    FROM BSIS
   for all entries in lt_tab
    WHERE BUKRS EQ SO_CCODE
    AND hkont ne c_hkont
    AND GJAHR EQ LV_YEAR
    AND belnr eq lt_tab-belnr
    AND MWSKZ IN SO_TXCDE
    AND BUDAT IN SO_POSTP.

  endif.

Re: Performance in BSIS

Former Member — Wed, 17 Oct 2007 05:48:31 GMT

Hi,

Can you check other alternative of using other table not BSIS as in actual production env you may face more performance issue.

Regards,

Atish

Re: Performance in BSIS

Former Member — Wed, 17 Oct 2007 06:01:38 GMT

Before deleting the data from lt_tab, sort the internal table.

And also delete the duplicate belnr entries.

As below.

SELECT a~mandt

a~belnr

A~BUZEI

a~mwskz

a~budat

A~SHKZG

A~DMBTR

b~lifnr

B~NAME1

b~ktokk

APPENDING CORRESPONDING

FIELDS OF TABLE lt_tab

FROM bsak AS a

INNER JOIN lfa1 AS b

ON a_{lifnr = b}lifnr

WHERE a~bukrs EQ so_ccode

AND A~GJAHR EQ LV_YEAR

AND a~budat IN so_postp

AND MWSKZ IN SO_TXCDE.

SORT lt_tab1 BY lifnr belnr.

SORT LT_TAB BY KTOKK.

DELETE LT_TAB WHERE KTOKK EQ 'UEMP'.

SORT LT_TAB BY BELNR.

DELETE ADJACENT DUPLICATES FROM LT_TAB COMPARING BELNR.

if not lt_tab[] is initial.

SELECT BELNR

MWSKZ

SHKZG

DMBTR

WAERS

HKONT

INTO CORRESPONDING FIELDS OF TABLE LT_TAB2_TEMP

FROM BSIS

for all entries in lt_tab

WHERE BUKRS EQ SO_CCODE

AND hkont ne c_hkont

AND GJAHR EQ LV_YEAR

AND belnr eq lt_tab-belnr

AND MWSKZ IN SO_TXCDE

AND BUDAT IN SO_POSTP.

endif.

Re: Performance in BSIS

Former Member — Wed, 17 Oct 2007 06:41:33 GMT

1. you are using "hkont ne c_hkont" in the where condition, as far as I remember NE can not be supported by the index

2. AUGDT & AUGBL are - if I remember correct - in BSIS always 00000000 or space

3. check how ZUONR is filled in your system; if filled in vendor line and G/L account line equal select in the join also ZUONR into lt_tab

so my suggestion on the BSIS code:



   SELECT BELNR
           MWSKZ
           SHKZG
           DMBTR
           WAERS
           HKONT
    INTO CORRESPONDING FIELDS OF TABLE LT_TAB2_TEMP
    FROM BSIS
   for all entries in lt_tab
    WHERE BUKRS EQ SO_CCODE
    AND hkont in r_hkont                             "your accounts needed 
    and augdt EQ '00000000'               "please review if they are really always initial
    and augbl EQ space                     "please review if they are really always initial
    and zuonr EQ lt_tab-zuonr            "only if filled equal as BSAK otherwise not!
    AND GJAHR EQ LV_YEAR
    AND belnr eq lt_tab-belnr
    AND MWSKZ IN SO_TXCDE
    AND BUDAT IN SO_POSTP.

Re: Performance in BSIS

Former Member — Wed, 17 Oct 2007 07:48:51 GMT

Thanks Daniel,

But do you think even if they have numbers of gl account, I should include it in the filter?

Re: Performance in BSIS

Former Member — Wed, 17 Oct 2007 11:34:22 GMT

Even without testing yes I think you should.

But I think best way that you try out both options on your system and then choose the better one for the final version of the program.