10-20-2006 5:20 PM
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.
10-20-2006 5:26 PM
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
10-20-2006 5:24 PM
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
10-20-2006 5:26 PM
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
10-23-2006 2:09 PM
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......
10-23-2006 9:04 PM
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