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.
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
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
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
Add a comment