topic Re: Problems selecting bsis data in Application Development and Automation Discussions

Problems selecting bsis data

Former Member — Mon, 03 Dec 2007 09:24:42 GMT

Hi all,

I'm taking a data from table bsis and bsas but it's make my program run very slow... When I check for the problem, I found out that when reading from table bsis was very slow. Is there any ways or solution for this problem? Here is my coding in selection from bsis. Any suggestion or help needed here....

SELECT b~monat

b~hkont

b~gsber

b~geber

b~bschl

b~shkzg

b~budat

b~dmbtr

b~fkber

b~blart

b~belnr

APPENDING CORRESPONDING FIELDS OF TABLE itaba

FROM bsis AS b

INNER JOIN ska1 AS c ON c~ktopl = 'FGCA' AND

c_{saknr = b}hkont

WHERE b~bukrs = p_bukrs

AND b~gjahr = p_gjahr

AND b~budat LE s_budat-high

AND b~monat LE p_monat

AND b~hkont IN s_hkont

and b~gsber in s_gsber.

thanks,

saiful.

Re: Problems selecting bsis data

Former Member — Mon, 03 Dec 2007 09:34:47 GMT

Hi!

BUDAT, MONAT and GSBER fields are not the part of the BSIS key and could slow it radically. Try to remove them from this select and then delete the unwanted entries from the internal table.

Regards

Tamá

Re: Problems selecting bsis data

Former Member — Mon, 03 Dec 2007 09:53:41 GMT

Create an Index on BSIS via SE11 on :

MANDT, BUDAT, MONAT, GSBER.

This will speed-up your query.

Re: Problems selecting bsis data

ThomasZloch — Mon, 03 Dec 2007 11:50:25 GMT

Hi,

why are you joining SKA1, you do not seem to select from it in your code.

Apart from that, if BSIS is fairly large, you want to select from it only when there is a rather low number of account numbers in S_HKONT. If there are many, but instead the selected posting dates in S_BUDAT is narrow, you should access the documents by BKPF/BSEG, as explained in previous posts in this forum.

Creating an index on BSIS should be your very last option, since this will eat storage space and add DB load on each table update.

Also, "b_{budat LE s_budat-high" seems strange, why not use "b}budat IN s_budat"?

Cheers

Thomas

Re: Problems selecting bsis data

Former Member — Mon, 03 Dec 2007 14:27:31 GMT

Before you do the SELECT, try:

CHECK NOT s_hkont IS INITIAL.

Even this may not help if s_hkont is completed, but will be picking up large amounts of data.

Rob

Re: Problems selecting bsis data

Former Member — Mon, 03 Dec 2007 16:45:49 GMT

Try using the following code.

TYPES: BEGIN OF ty_bsis,
         hkont  TYPE bsis-hkont,
         monat  TYPE bsis-monat,
         gsber  TYPE bsis-gsber,
         geber  TYPE bsis-geber,
         bschl  TYPE bsis-bschl,
         shkzg  TYPE bsis-shkzg,
         budat  TYPE bsis-budat,
         dmbtr  TYPE bsis-dmbtr,
         fkber  TYPE bsis-fkber,
         blart  TYPE bsis-blart,
         belnr  TYPE bsis-belnr,
         del(1) TYPE c         ,
       END OF ty_bsis,

       BEGIN OF ty_ska1,
         saknr TYPE ska1-saknr,
       END OF ty_ska1.

DATA: w_bsis     TYPE                 ty_bsis ,
      w_index    TYPE                 sy-tabix,
      w_found(1) TYPE                 c       ,

      t_bsis     TYPE        TABLE OF ty_bsis ,
      t_bsis_tmp TYPE        TABLE OF ty_bsis ,
      t_ska1     TYPE HASHED TABLE OF ty_ska1
        WITH UNIQUE KEY saknr.

SELECT hkont
       monat
       gsber
       geber
       bschl
       shkzg
       budat
       dmbtr
       fkber
       blart
       belnr
  FROM bsis
  INTO TABLE t_bsis
  WHERE bukrs EQ p_bukrs
  AND   hkont IN s_hkont
  AND   gjahr EQ p_gjahr
  AND   budat IN s_budat
  AND   monat LE p_monat
  AND   gsber IN s_gsber.

IF sy-subrc EQ 0.

  t_bsis_tmp[] = t_bsis[].

  SORT t_bsis_tmp BY hkont.

  DELETE ADJACENT DUPLICATES FROM t_bsis_tmp COMPARING hkont.

  SELECT saknr
    FROM ska1
    INTO TABLE t_ska1
    FOR ALL ENTRIES IN t_bsis_tmp
    WHERE ktopl EQ 'FGCA'
    AND   saknr EQ t_bsis_tmp-hkont.


  LOOP AT t_bsis INTO w_bsis.

    w_index = sy-tabix.

    AT NEW hkont.

      READ TABLE t_ska1 WITH KEY saknr = w_bsis-hkont
                                 TRANSPORTING NO FIELDS.

      IF sy-subrc EQ 0.
        w_found = 'X'.
      ELSE.
        CLEAR w_found.
      ENDIF.

    ENDAT.

    CASE w_found.
      WHEN space.
        w_bsis-del = 'X'.
        MODIFY t_bsis FROM w_bsis INDEX w_index TRANSPORTING del.
    ENDCASE.

  ENDLOOP.

  DELETE t_bsis WHERE del EQ 'X'.

ENDIF.

Re: Problems selecting bsis data

Former Member — Fri, 14 Dec 2007 09:25:58 GMT

Thanks for all that been responsed to my question.. I already found out the best way to my problems.. Thanks for helping

Re: Problems selecting bsis data

Former Member — Mon, 07 Jan 2008 11:46:21 GMT

Hi Muhammad.

Can u please tell me how you resolve this problem..because i am also facing problen while fetching data from BSIS.

Thanks.

Re: Problems selecting bsis data

Former Member — Wed, 09 Jan 2008 08:14:17 GMT

Hi Muhammad Saiful,

Could you share with us how you solve the performance issue because i also having the same problem.

Your help much highly appreciated.

Thank you.