4 Replies Latest reply: Oct 23, 2006 10:04 PM by Vijay Shetty RSS

BSEG extract

Syed Rahman
Currently Being Moderated

BSEG is a cluster table and I need to improve the performance of the extract as given below. This is really taking a long time to run. Any suggestions as to how I can improve the performance?

 

 

SELECT BELNR

       BUZEI

       BSCHL

       SHKZG

       PRCTR

       KOSTL

       HKONT

       DMBTR

       WRBTR

       SGTXT

    FROM  BSEG

    INTO  TABLE IBSEG

    FOR ALL entries in IBKPF

    WHERE BELNR = IBKPF-BELNR

    AND HKONT in gl_acct.

  • Re: BSEG extract
    Rich Heilman
    Currently Being Moderated

    Add BUKRS to the where clause, this should help.  If you don't current have BUKRS in the ibkpf internal table, you should add it.

     

     

    <b>sort ibkpf ascending bukrs belnr.</b>
    
    SELECT BELNR BUZEI BSCHL SHKZG
           PRCTR KOSTL HKONT DMBTR
           WRBTR SGTXT 
             FROM BSEG
                 INTO TABLE IBSEG
                     FOR ALL entries in IBKPF
                         WHERE<b> bukrs = ibkpf-bukrs</b>
                           and BELNR = IBKPF-BELNR
                           AND HKONT in gl_acct.

     

    Regards,

    Rich Heilman

  • Re: BSEG extract
    Narendran Muthukumaran
    Currently Being Moderated

    Hi,

     

    In the IBKPF ..if you have BUKRS GJAHR use them in your where clause..

     

    <b>IF NOT IBKPF[] IS INITIAL.</b>

    SELECT BELNR

    BUZEI

    BSCHL

    SHKZG

    PRCTR

    KOSTL

    HKONT

    DMBTR

    WRBTR

    SGTXT

    FROM BSEG

    INTO TABLE IBSEG

    FOR ALL entries in IBKPF

    <b>WHERE BUKRS = IBKPF-BUKRS

    AND   BELNR = IBKPF-BELNR

    AND   GJAHR = IBKPF-GJAHR</b>

    AND HKONT in gl_acct.

     

    <b>ENDIF.</b>

     

    If it is not there in the internal table IBKPF..Add them...

     

    Also check the internal table IBKPF is not initial before using it for BSEG.

     

    Thanks,

    Naren

  • Re: BSEG extract
    Poorna Poorna
    Currently Being Moderated

    Hi,

     

    Normally accessing BSEG always performance problem. I will prefer BSIS and BSAD insted of BSEG. I have seen all fields in your select query is avial in BSIS.

     

    please check out and let us know.

     

    .....Poorna......

  • Re: BSEG extract
    Vijay Shetty
    Currently Being Moderated

    Here is a program that I wrote for extracting

    data from BSEG. There was a part which had

    some "summarized extract", which I have not posted.

     

    ***************************

    REPORT   zbseg_extract
             LINE-SIZE 100
             LINE-COUNT 65
             NO STANDARD PAGE HEADING.
    
    TYPE-POOLS : sscr.                      
    
    TYPES : BEGIN OF ty_bkpf,
             bukrs      TYPE bkpf-bukrs,         "COMPANY CODE(4)
             belnr      TYPE bkpf-belnr,         "DOCUMENT NUMBER(10)
             gjahr      TYPE bkpf-gjahr,         "FISCAL YEAR(4)
             blart      TYPE bkpf-blart,         "DOC TYPE(2)
             bldat      TYPE bkpf-bldat,         "DOC DATE(10)
             budat      TYPE bkpf-budat,         "POSTING DATE(10)
             monat      TYPE bkpf-monat,                        "PERIOD(2)
             usnam      TYPE bkpf-usnam,         "USER NAME(12)
             tcode      TYPE bkpf-tcode,                        "TCODE(20)
             xblnr      TYPE bkpf-xblnr,         "REF DOC #(16)
             stjah      TYPE bkpf-stjah,         "REVERSE DOC FICAL YR(4)
             bktxt      TYPE bkpf-bktxt,         "DOC HEADER TEXT(25)
             waers      TYPE bkpf-waers,         "CURRENCY KEY(5)
             kursf      TYPE bkpf-kursf,         "EXCHG RATE(12)
             bstat      TYPE bkpf-bstat,         "DOC STATUS(1)
            END OF ty_bkpf.
    
    TYPES : BEGIN OF ty_bseg,
             bukrs      TYPE bkpf-bukrs,         "COMPANY CODE
             belnr      TYPE bkpf-belnr,         "DOCUMENT NUMBER
             gjahr      TYPE bkpf-gjahr,         "FISCAL YEAR
             buzei      TYPE bseg-buzei,         "FI LINE ITEM(3)
             augdt      TYPE bseg-augdt,         "CLEARING DATE(10)
             augcp      TYPE bseg-augcp,         "CLEARING ENTRY DATE(10)
             augbl      TYPE bseg-augbl,         "DOC # OF CLEARING DOC(10)
             bschl      TYPE bseg-bschl,         "POSTING KEY(2)
             koart      TYPE bseg-koart,         "ACCOUNT TYPE(1)
             shkzg      TYPE bseg-shkzg,         "DR/CR INDIC(1)
             dmbtr      TYPE bseg-dmbtr,         "AMT IN LOCAL CURRENCY(16)
    *        sgtxt      TYPE bseg-sgtxt,         "ITEM TEXT(50)
             bewar      TYPE bseg-bewar,         "TRANSACTION TYPE(3)
             kostl      TYPE bseg-kostl,         "COST CENTER(10)
             saknr      TYPE bseg-saknr,         "G/L ACCOUNT NUMBER(10)
             hkont      TYPE bseg-hkont,         "GENERAL LEDGR ACCT(10)
            END OF ty_bseg.
    
    * Itab of file names
    TYPES: BEGIN OF ty_file,
            file LIKE rlgrap-filename,
           END OF ty_file.
    
    * Result of system command
    TYPES: BEGIN OF ty_result,
            line(132) TYPE c,
           END OF ty_result.
    
    TYPES: BEGIN OF ty_sum_bkpf,
             bukrs TYPE bkpf-bukrs, "Company Code
             belnr TYPE bkpf-belnr, "DOCUMENT NUMBER
             gjahr TYPE bkpf-gjahr, "Fiscal year
             blart TYPE bkpf-blart, "Document type
             monat TYPE bkpf-monat, "Period
           END OF ty_sum_bkpf,
    
           BEGIN OF ty_sum_bseg,
             bukrs TYPE bseg-bukrs, "COMPANY CODE
             belnr TYPE bseg-belnr, "DOCUMENT NUMBER
             gjahr TYPE bseg-gjahr, "FISCAL YEAR
             hkont TYPE bseg-hkont, "GENERAL LEDGR ACCT
             dmbtr TYPE bseg-dmbtr, "AMT IN LOCAL CURRENCY
           END OF ty_sum_bseg.
    
    DATA: t_bkpf TYPE TABLE OF ty_bkpf,
          t_bseg TYPE TABLE OF ty_bseg,
          t_file TYPE TABLE OF ty_file,
          t_result TYPE TABLE OF ty_result,
          t_sum_bkpf TYPE TABLE OF ty_sum_bkpf,
          t_sum_bseg TYPE TABLE OF ty_sum_bseg.
    
    
    DATA   wa_file LIKE LINE OF t_file.
    DATA   wa_result LIKE LINE OF t_result.
    DATA   w_cmd(132) TYPE c.  "unix command
    DATA   gv_current_file LIKE rlgrap-filename.
    
    FIELD-SYMBOLS: <bkpf> TYPE ty_bkpf,
                   <bseg> TYPE ty_bseg,
                   <bkpf_s> TYPE ty_sum_bkpf,
                   <bseg_s> TYPE ty_sum_bseg.
    
    
    DATA: gc_bkpf       TYPE cursor,
          wa_out(1000)  TYPE c,
          lv_belnr      LIKE bseg-belnr,
          lv_kursf(16)  TYPE c,
          lv_dmbtr(20)  TYPE c,
          w_monat       LIKE bkpf-monat,
          lv_dcpfm      LIKE usr01-dcpfm,  "Decimal point format
          w_bkpf_cnt(9) TYPE n,            "# of records from BKPF
          w_bseg_cnt(9) TYPE n,            "# of records from BSEG
          w_max         TYPE i VALUE 1000,
          gv_error(1)   TYPE c,
          w_current_size(9) TYPE n,
          gv_file_ct(8)    TYPE n VALUE '1',  "initial value of file suffix
          wa_bkpf       TYPE bkpf,                 
          lv_flag.                                 
    
    DATA : ls_restriction  TYPE sscr_restrict,
           ls_opt_list     TYPE sscr_opt_list,
           ls_ass          TYPE sscr_ass.
    
    CONSTANTS c_pipe(1)  TYPE c VALUE '|'.
    CONSTANTS: c_x(1)    TYPE c VALUE 'X',
               c_option(2)      VALUE 'EQ',
               c_sign(1)        VALUE 'I',
               c_yes(1)         VALUE '1',
               c_no(1)          VALUE '0',
               c_txt(4)  TYPE c VALUE '.TXT',
               c_gz(3)   TYPE c VALUE '.gz',
               c_selgrp(3)      VALUE 'SEL',
               c_pargrp(3)      VALUE 'PAR',
               c_cmd1(15) TYPE c VALUE 'gzip -v -S .gz',
               c_cmd2(8)  TYPE c VALUE '2>&1',
               c_noint(10)      VALUE 'NOINTERVLS',
               c_s(1)           VALUE 'S',
               c_name(7)        VALUE 'S_BLART',
               c_modgrp(3)      VALUE 'MOD'.
    
    SELECTION-SCREEN BEGIN OF BLOCK blk WITH FRAME TITLE text-t03.
      PARAMETERS p_detail RADIOBUTTON GROUP rad USER-COMMAND rem.
      PARAMETERS p_sum    RADIOBUTTON GROUP rad.
    SELECTION-SCREEN END   OF BLOCK blk.
    
    SELECTION-SCREEN BEGIN OF BLOCK one WITH FRAME TITLE text-t01.
    
    PARAMETERS p_bukrs LIKE bkpf-bukrs OBLIGATORY default 'US15'.
    *  SELECT-OPTIONS
    *             s_belnr FOR  lv_belnr.
    PARAMETERS p_gjahr LIKE bkpf-gjahr OBLIGATORY default sy-datum(4).
    PARAMETERS p_monat LIKE bkpf-monat OBLIGATORY
                       MODIF ID par default sy-datum+4(2).
    SELECT-OPTIONS
               s_monat FOR w_monat
                       MODIF ID sel.
    SELECT-OPTIONS                           
               s_blart FOR wa_bkpf-blart     
               NO INTERVALS MODIF ID mod.   
    SELECTION-SCREEN SKIP.
    PARAMETERS p_file  LIKE rlgrap-filename OBLIGATORY
                       DEFAULT text-001.
    SELECTION-SCREEN END OF BLOCK one.
    
    SELECTION-SCREEN SKIP.
    SELECTION-SCREEN BEGIN OF BLOCK two WITH FRAME TITLE text-t02.
    PARAMETERS p_size  TYPE i OBLIGATORY
                       DEFAULT w_max.
    PARAMETERS p_count TYPE i OBLIGATORY   "Count on the number of
                       DEFAULT '20000'.    "BSEG records per file
    PARAMETERS p_zip   TYPE c  AS CHECKBOX DEFAULT c_x.
    SELECTION-SCREEN END OF BLOCK two.
    
    INITIALIZATION.
      CLEAR w_bkpf_cnt.
      CLEAR w_bseg_cnt.
      CLEAR p_monat.
      REFRESH s_monat.
      REFRESH t_file.
    
    * Restrict the select-options S_BLART
      CLEAR ls_opt_list.
      ls_opt_list-name = c_noint.
      ls_opt_list-options-eq = c_x.
      APPEND ls_opt_list TO ls_restriction-opt_list_tab.
    
      CLEAR ls_ass.
      ls_ass-kind    = c_s.
      ls_ass-name    = c_name.
      ls_ass-sg_main = c_sign.
      ls_ass-sg_addy = space.
      ls_ass-op_main = c_noint.
      ls_ass-op_addy = c_noint.
      APPEND ls_ass    TO ls_restriction-ass_tab.
    
      CLEAR ls_opt_list.
      CLEAR ls_ass.
    
      CALL FUNCTION 'SELECT_OPTIONS_RESTRICT'
        EXPORTING
          restriction            = ls_restriction
        EXCEPTIONS
          too_late               = 1
          repeated               = 2
          selopt_without_options = 3
          selopt_without_signs   = 4
          invalid_sign           = 5
          empty_option_list      = 6
          invalid_kind           = 7
          repeated_kind_a        = 8
          OTHERS                 = 9.
    
    AT SELECTION-SCREEN.
      IF p_detail EQ c_x.
        if s_blart[] is initial.
          MESSAGE e257(F0) WITH text-013 text-014.
        endif.
      ELSEIF p_sum EQ c_x.
      ENDIF.
    
    START-OF-SELECTION.
      REFRESH t_bkpf.
      REFRESH t_sum_bkpf.            
    
      CLEAR   lv_dcpfm.
    
    * First file name
      CONCATENATE p_file gv_file_ct c_txt INTO gv_current_file.
    
      OPEN DATASET gv_current_file IN TEXT MODE FOR OUTPUT ENCODING DEFAULT.
      IF sy-subrc NE 0.
        gv_error = c_x.
        FORMAT COLOR COL_NEGATIVE.
        WRITE : text-002, gv_current_file.
        STOP.
      ELSE.
        CLEAR wa_out.
        CONCATENATE text-010 gv_current_file
                    INTO wa_out SEPARATED BY space.
        TRANSFER wa_out TO gv_current_file LENGTH 1000.
    
        CLEAR wa_out.
        IF p_detail EQ c_x.
          CONCATENATE text-h01 text-h02 text-h03
                 INTO wa_out SEPARATED BY c_pipe.
        else.
          MOVE text-s01 TO wa_out.
        endif.
        TRANSFER wa_out TO gv_current_file LENGTH 1000.
    
      ENDIF.
    
      IF sy-sysid = c_yrp.
        REFRESH s_monat.
        s_monat-sign = c_sign.
        s_monat-option = c_option.
        s_monat-low = p_monat.
        APPEND s_monat.
        CLEAR: p_monat, s_monat.
      ENDIF.
    
      IF p_detail EQ c_x.
        perform do_detailed_extract.
      ELSEIF p_sum EQ c_x.
        perform do_summarized_extract.
      endif.
    
      CLOSE DATASET gv_current_file.
    
      IF NOT p_zip IS INITIAL.
    * zip the file just created.
        CLEAR w_cmd.
        REFRESH t_result.
        CONCATENATE c_cmd1 gv_current_file c_cmd2
               INTO w_cmd SEPARATED BY space.
        CALL 'SYSTEM' ID 'COMMAND' FIELD w_cmd
        ID 'TAB' FIELD t_result[].
        WRITE : /1 w_cmd COLOR COL_POSITIVE INTENSIFIED OFF.
        LOOP AT t_result INTO wa_result.
          WRITE /5 wa_result
             COLOR COL_POSITIVE INTENSIFIED ON.
        ENDLOOP.
        CONCATENATE gv_current_file c_gz INTO gv_current_file.
      ENDIF.
    * Retain last file name
      CLEAR wa_file.
      MOVE gv_current_file TO wa_file.
      APPEND wa_file TO t_file.
      CLEAR wa_file.
    
    
    END-OF-SELECTION.
      IF gv_error IS INITIAL.
    *   Write out the selection screen parameters
        WRITE:/ text-004 COLOR COL_HEADING,
                p_bukrs  COLOR COL_TOTAL INTENSIFIED OFF,
              / text-005 COLOR COL_HEADING,
                p_gjahr  COLOR COL_TOTAL INTENSIFIED OFF,
              / text-011 COLOR COL_HEADING,
                s_monat-low COLOR COL_TOTAL INTENSIFIED OFF,
              / text-006 COLOR COL_HEADING.
    *   Display all files generated
        CLEAR wa_file.
        LOOP AT t_file INTO wa_file.
          IF sy-tabix = 1.
            WRITE: 15 wa_file COLOR COL_TOTAL INTENSIFIED OFF.
          ELSE.
            WRITE: /15 wa_file COLOR COL_TOTAL INTENSIFIED OFF.
          ENDIF.
          CLEAR wa_file.
        ENDLOOP.
    
    *   # of records processed from BKPF, BSEG
        SKIP 2.
        WRITE: / text-007,
                    w_bkpf_cnt COLOR COL_POSITIVE INTENSIFIED OFF,
               / text-008,
                    w_bseg_cnt COLOR COL_POSITIVE INTENSIFIED OFF.
      ELSE.
        WRITE text-009 COLOR COL_NEGATIVE.
      ENDIF.
    
    *&---------------------------------------------------------------------*
    *&      Form  do_detailed_extract
    *&---------------------------------------------------------------------*
    *       text
    *----------------------------------------------------------------------*
    *  -->  p1        text
    *  <--  p2        text
    *----------------------------------------------------------------------*
    FORM do_detailed_extract .
    *---------------------------------------------------------*
    *                Detailed Extract                         *
    *---------------------------------------------------------*
      OPEN CURSOR gc_bkpf FOR
           SELECT bukrs
                  belnr
                  gjahr
                  blart
                  bldat
                  budat
                  monat
                  usnam
                  tcode
                  xblnr
                  stjah
                  bktxt
                  waers
                  kursf
                  bstat
             FROM bkpf
            WHERE bukrs EQ p_bukrs
    *       AND belnr IN s_belnr
              AND gjahr EQ p_gjahr
              AND blart IN s_blart             
              AND monat IN s_monat.
      CLEAR w_max.
      w_max  = p_size.
    
      CLEAR w_current_size.
    
      DO.
        FETCH NEXT   CURSOR gc_bkpf
              INTO   TABLE  t_bkpf
             PACKAGE SIZE   w_max.
    
        IF sy-subrc NE 0.
          CLOSE CURSOR gc_bkpf.
          EXIT.
        ELSEIF sy-subrc EQ 0.
          REFRESH t_bseg.
          IF NOT t_bkpf[] IS INITIAL.
            SELECT bukrs
                   belnr
                   gjahr
                   buzei
                   augdt
                   augcp
                   augbl
                   bschl
                   koart
                   shkzg
                   dmbtr
    *              sgtxt
                   bewar
                   kostl
                   saknr
                   hkont
              FROM bseg
              INTO TABLE t_bseg
               FOR ALL ENTRIES IN t_bkpf
             WHERE bukrs EQ t_bkpf-bukrs
               AND belnr EQ t_bkpf-belnr
               AND gjahr EQ t_bkpf-gjahr.
            IF sy-subrc EQ 0.
    *       PREPARE THE EXTRACT
              LOOP AT t_bkpf ASSIGNING <bkpf>.
                w_bkpf_cnt = w_bkpf_cnt + 1.
                LOOP AT t_bseg ASSIGNING <bseg>
                              WHERE bukrs EQ <bkpf>-bukrs
                                AND belnr EQ <bkpf>-belnr
                                AND gjahr EQ <bkpf>-gjahr.
                  CLEAR wa_out.
                  WRITE <bkpf>-kursf TO lv_kursf.
                  WRITE <bseg>-dmbtr TO lv_dmbtr.
                  CONCATENATE <bkpf>-bukrs
                              <bkpf>-belnr
                              <bkpf>-gjahr
                              <bseg>-buzei
                              <bkpf>-blart
                              <bkpf>-bldat
                              <bkpf>-budat
                              <bkpf>-monat
                              <bkpf>-usnam
                              <bkpf>-tcode
                              <bkpf>-xblnr
                              <bkpf>-stjah
                              <bkpf>-bktxt
                              <bkpf>-waers
                              lv_kursf
                              <bkpf>-bstat
                              <bseg>-augdt
                              <bseg>-augcp
                              <bseg>-augbl
                              <bseg>-bschl
                              <bseg>-koart
                              <bseg>-shkzg
                              lv_dmbtr
    *                         <bseg>-sgtxt
                              <bseg>-bewar
                              <bseg>-kostl
                              <bseg>-saknr
                              <bseg>-hkont
                         INTO wa_out SEPARATED BY c_pipe.
                  REPLACE ',' IN wa_out WITH ''.
                  TRANSFER wa_out TO gv_current_file LENGTH 1000.
                  w_bseg_cnt = w_bseg_cnt + 1.
    *           Logic to split files as per Count on sel. screen
                  w_current_size = w_current_size + 1.
                  IF w_current_size EQ p_count.
                    CLEAR w_current_size.
                    CLEAR wa_out.
    *             Denote end of current file
                    CONCATENATE text-003 gv_file_ct INTO wa_out.
                    TRANSFER wa_out TO gv_current_file.
                    CLOSE DATASET gv_current_file.
    
                    IF NOT p_zip IS INITIAL.
    *             zip the file just closed.
                      CLEAR w_cmd.
                      REFRESH t_result.
                      CONCATENATE c_cmd1 gv_current_file c_cmd2
                             INTO w_cmd SEPARATED BY space.
                      CALL 'SYSTEM' ID 'COMMAND' FIELD w_cmd
                      ID 'TAB' FIELD t_result[].
                      WRITE /1 w_cmd  COLOR COL_POSITIVE INTENSIFIED OFF.
                      LOOP AT t_result INTO wa_result.
                        WRITE /5 wa_result
                           COLOR COL_POSITIVE INTENSIFIED ON.
                      ENDLOOP.
                    CONCATENATE gv_current_file c_gz INTO gv_current_file.
                    ENDIF.
    *             Retain name of current file
                    CLEAR wa_file.
                    MOVE gv_current_file TO wa_file.
                    APPEND wa_file TO t_file.
                    CLEAR wa_file.
    
                    CLEAR: wa_out, gv_current_file.
                   gv_file_ct = gv_file_ct + 1.    "increment file counter
                    CONDENSE gv_file_ct.
                    CONCATENATE p_file gv_file_ct c_txt
                           INTO gv_current_file.
    *             Open next file
                    OPEN DATASET gv_current_file IN TEXT MODE
                                        FOR OUTPUT ENCODING DEFAULT.
                    IF sy-subrc NE 0.
                      gv_error = c_x.
                      FORMAT COLOR COL_NEGATIVE.
                      WRITE : text-002, gv_current_file.
                      STOP.
                    ELSE.
                      CLEAR wa_out.
                      CONCATENATE text-010 gv_current_file
                                  INTO wa_out SEPARATED BY space.
                      TRANSFER wa_out TO gv_current_file LENGTH 1000.
                      CLEAR wa_out.
                      CONCATENATE text-h01 text-h02 text-h03
                                  INTO wa_out SEPARATED BY c_pipe.
                      TRANSFER wa_out TO gv_current_file LENGTH 1000.
                    ENDIF.
    *             End of file split logic
                  ENDIF.
                ENDLOOP.
                DELETE t_bseg WHERE bukrs EQ <bkpf>-bukrs
                                AND belnr EQ <bkpf>-belnr
                                AND gjahr EQ <bkpf>-gjahr.
              ENDLOOP.
              CLEAR wa_out.
              CONCATENATE text-003 gv_file_ct INTO wa_out.
              TRANSFER wa_out TO gv_current_file.
            ENDIF. "END OF SY-SUBRC CHECK
          ENDIF.
          REFRESH: t_bkpf,  t_bseg.
        ENDIF. " ELSEIF SY-SUBRC EQ 0.
      ENDDO.   " DO
    
    ENDFORM.                    " do_detailed_extract
    

     

Actions