Optimization Issue for BKPF and BSEG tables

[Former Member]

Hi All,

Following code taking too much time to fetch data from BKPF and BSEG table,

please review this code and give some useful tips to optimize it:

FORM data_fetch.

  SELECT

          bukrs     " Company Code
          belnr     " Accounting Document Number
          gjahr
          bktxt     " Document Header Text
          xblnr     " Reference Document Number
          blart     " Document Type
          bldat     " Document Date
          budat     " Posting Date
          usnam     " User Name
    INTO TABLE it_bkpf
     FROM bkpf
     WHERE bukrs IN s_bukrs
     AND   belnr IN s_belnr
     AND   GJAHR IN s_gjahr
     AND   budat IN s_budat
     AND   bldat IN s_bldat  .

  SORT it_bkpf BY bukrs belnr gjahr budat.



  IF NOT it_bkpf[] IS INITIAL.

    SELECT  bukrs
            belnr
            gjahr
            mwskz    " Tax Code
            buzei
            zuonr     " Assignment Number
            sgtxt     " Item Text
*            dmbtr     " Amount in Document Currency
            dmbtr
            prctr     " Profit Center
            hkont     " GL CODE
            ktosl     " Transaction key
            SHKZG
     FROM bseg
     INTO TABLE it_bseg
     FOR ALL ENTRIES IN it_bkpf
     WHERE bukrs = it_bkpf-bukrs
     AND belnr = it_bkpf-belnr
     AND gjahr = it_bkpf-gjahr
     AND hkont IN  s_hkont1  "Bank Commission Code
*and hkont <> it_bseg1-hkont
      and  mwskz in s_mwskz.

    SORT it_bseg BY bukrs belnr gjahr.

  IF sy-subrc NE 0.
    MESSAGE 'No records for given selection' TYPE 'I'.
   leave screen.
  ENDIF.

  SELECT
            bukrs
            belnr
            gjahr
            mwskz    " Tax Code
            buzei
            zuonr     " Assignment Number
            sgtxt     " Item Text
*            dmbtr     " Amount in Document Currency
            dmbtr
            prctr     " Profit Center
            hkont     " GL CODE
            ktosl     " Transaction key
            SHKZG
     FROM bseg
     INTO TABLE it_bseg1
     FOR ALL ENTRIES IN it_bseg
     WHERE    bukrs = it_bseg-bukrs
     and      belnr = it_bseg-belnr
     AND      gjahr = it_bseg-gjahr
     and      hkont <> it_bseg-hkont
     AND      hkont IN s_hkont.         " Bank Code
sort it_bseg1 by  bukrs belnr gjahr.


   IF sy-subrc = 0.
    select skb1~SAKNR
           skb1~HBKID
    into table skb1_t012
    from skb1 join t012
    on skb1~hbkid = t012~hbkid
    for all entries in it_bseg1
    where skb1~saknr = it_bseg1-hkont
    and skb1~bukrs = it_bseg1-bukrs.
    sort skb1_t012 by saknr hbkid .
    else.
      MESSAGE 'Wrong Bank Code !' TYPE 'E'.
      LEAVE SCREEN.
    ENDIF.

       SELECT  bukrs
            belnr
            gjahr
            mwskz    " Tax Code
            buzei
            zuonr     " Assignment Number
            sgtxt     " Item Text
*            dmbtr     " Amount in Document Currency
            dmbtr
            prctr     " Profit Center
            hkont     " GL CODE
            ktosl     " Transaction key
            SHKZG
     FROM bseg
     INTO TABLE it_bseg2
     FOR ALL ENTRIES IN it_bseg
     WHERE    bukrs = it_bseg-bukrs
     and      belnr = it_bseg-belnr
     AND      gjahr = it_bseg-gjahr
     AND ( ktosl = 'VS7' OR ktosl = 'VS8' OR ktosl = 'VSE' ).
*   and  mwskz in s_mwskz.
    SORT it_bseg2 BY  bukrs belnr gjahr.
*BREAK-POINT.

endif.

Edited by: Julius Bussche on Feb 17, 2009 1:09 PM

Please use meaningfull subject titles and code tags

[Former Member]

Hi,

I agree BSEG table will always be an optimization issue...

Why dont you try for other related tables to fetch the required data?

Below arer list of some tables that have same data as in BSEG but specific data(Vendor/customers/GL account/ open/closed)..

BSIK :(Accounting: Secondary index for

vendors)

BSAK:

(Accounting: Secondary index for

vendors (cleared items))

BSAS

(Accounting: Secondary index for G/L

accounts (cleared items))

BSIS: Open GL accounts

BSID

(Accounting: Secondary index for

customers)

BSAD

(Accounting: Secondary index for

customers (cleared items))

Try to avoid BSEG and use the above tables based on data in BKPF...

Regards

Shiva

[Former Member]

Hi,

Concatenate First three Select statements into SELECT STATEMENT USING JOIN.

Note:More Select statements in your program more number of Database hits.

Hence more time for data retrieval.

Minimize the number of select quires Using Joins.

Best way is create a Database view..Coz Views are buffered at application server.

Regards,

Gurpreet

[Former Member]

>

> Hi,
> 
> Concatenate First three Select statements into SELECT STATEMENT USING JOIN.
> 
> Note:More Select statements in your program more number of Database hits.
> Hence more time for data retrieval.
> 
> Minimize the number of select quires Using Joins.
> 
> Best way is create a Database view..Coz Views are buffered at application server.
> 
> Regards,
> Gurpreet

Gurpreet - BSEG is a cluster table and cannot be used in either JOINs or views.

Rob

[Former Member]

1st Query: BKPF :Make sure s_bukrs and s_budat are mandatory.It will be better if u can hard code some value in the WHERE condition for BKPF-bstat ... IN ( ' ' , 'A' , 'B' )etc

2. Selection from BSEG is done thrice..try to make it as a single selection.

Declare another select options s_ hktotal no-display or LIKE Range and append both the values of s_hkont1 & s_hkont.(first BSEG selection)

it_tmp = it_bseg [] .& delete it_tmp WHERE hkont NOT IN s_hkont. ( 2nd BSEG selection)be avoided

it_tmp1 = it_bseg [].& delete it_tmp WHERE ktosl NOT IN ( 'VS7' , 'VS8' , 'VSE' ). ( 3nd BSEG selection) can be avoided.

Cheers

Edited by: Mukundan Ramanathan on Feb 17, 2009 2:35 PM

Edited by: Mukundan Ramanathan on Feb 17, 2009 2:36 PM

Edited by: Mukundan Ramanathan on Feb 17, 2009 2:37 PM

[Former Member]

Hi

Mukundan,

How can i append the value of paramter s_hkont and s_hkont1 into said variable.

Please mention syntax also.

Thks

Shailesh

[Former Member]

DATA : s_ hktotal  TYPE RANGE OF bseg-hkont .
DATA : wa_s_ hktotal  LIKE LINE OF bseg-hkont . 
DATA: wa_hkont LIKE LINE OF s_hkont.     

s_ hktotal []  = s_hkont1 [] .

LOOP AT s_hkont INTO wa_hkont.
APPEND wa_hkont TO s_ hktotal. " SO for BSEG select
ENDLOOP

select from bkpf should take more time.make sure both bukrs and budat are mandatory.

As said by thomas ..BSTAT = SPACE will make difference

Cheers

[Former Member]

Hi,

Mukundan

First of all thanks for your valuable tips,further can you give me any sample code related with this scenario,actually i am trying all the above mentioned steps and also performance increased but little bit.it should more optimized.

Thks

Shailesh

[Former Member]

Hi...i dont have the code.

Few points

1.Make use of SE30,ST05..Identify which programming block is taking time.(Data base selection or ABAP).,make use of binary search,FS.

2.Change the logic(why three int tables are required?) whereever applicable.

3.Running in back ground .

Cheers

[Former Member]

Dear Guys,

Thanks for cooperation,problem gets resolved.

Thks

Shailesh

[Former Member]

have a look at the open cursor statement... it might improve your performance a lot

[Former Member]

>

> have a look at the open cursor statement... it might improve your performance a lot

How??

Rob

[Former Member]

can u elaborate?

Regds

Shailesh

[Former Member]

just have a look at some standard BI (FI) extractors

[Former Member]

You could add some database indexes against the BSIK, BSAK, BSIS, BSAS, BSID and BSAD table which will create an alternative key if you are add HKONT into the index

This can be done via SE11 and would require you to build the indexes on the table afterwards using SE14.

Just be careful as this can create an overhead whilst cretaing the indexes so you might want to get some advice from a Basis person.

Regards

Larissa

[ThomasZloch]

    FROM BKPF
    WHERE bukrs IN s_bukrs
     AND   belnr IN s_belnr
     AND   GJAHR IN s_gjahr
     AND   budat IN s_budat
     AND   bldat IN s_bldat

How are these ranges filled? For BUKRS you want a single value, and also include BSTAT = SPACE in the WHERE-conditions, so that access by narrow BUDAT range can use the appropriate index.

As previously said, try to hit BSEG only once and not three times, rewrite your logic accordingly.

Thomas