Skip to Content
author's profile photo Former Member
Former Member

Optimization Issue for BKPF and BSEG tables

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

5 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Feb 17, 2009 at 10:45 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Feb 17, 2009 at 10:48 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

  • author's profile photo Former Member
    Former Member
    Posted on Feb 17, 2009 at 01:35 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Feb 17, 2009 at 04:40 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Feb 17, 2009 at 05:20 PM
        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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.