How to improve performance with large SAP tables

dataservices sap-erp-bw
1

With DS 3.0 and using SAP Rapid Marts… DI creates a flat file from SAP table with 800,00 rows (we need all the data) and as it starts writing to flat file, perf degrades since the system has to append to the end of the flat file… This is inherently poor process of getting data from database… Are there other methods to get the data? At the end, the flat file gets loaded into another warehouse database… in this case, sap is on oracle and DWH is also on oracle… why do we have to go thru sap table to flat file and flat file to oracle database? Any suggestions to improve perf would be helpful… There is no problem with ABAP code that DI generates… we have altered number of rows (buffer) but bottleneck is writing to the flat file? Any other design possibilities?

Thanks

diarch :us: (BOB member since 2009-01-24)

2

“append to the end of the flat file”

What’s wrong with that? An append does not take time at all…

What transport method do you use?
Can I see the respective sections of the trace log, where it says ABAP started, ABAP ended, dataflow ended,…?

Werner Daehn :de: (BOB member since 2004-12-17)

3

we are using standard PS_RM… two DF that have similar problems are project_status (Reading JEST table) and projectelementup and projectelementleft (reads PRHI and PRPS)… I went thru debugger with the generated ABAP… ECC process very quickly the reads into ITAB and then as it writes in chunk to flat file… it slows as it accumulates data… you are saying append should not take time… as you watch – CPU time increases once in 15 mins as the internal table gets processed… when ecc tables has 10 of millions rows… writing to flat file (you got reserve space on file system on ecc and on job server and updates databse)… why not write directly to target database…

example: when you extract from ECC to BW… the same process takes less than 5 mins to dump the data into ODS?

Thoughts?
Thanks

diarch :us: (BOB member since 2009-01-24)

4

My statement would be it is not the writing but the last query step does some expensive processing, e.g. lookup JEST table or so.

The BW Extractors you should not compare with the RapidMart directly as these are different ABAP programs that do different things. JEST table is a tricky one as it is usually large and has no delta indicator. It’s one of the reasons I really would like to be able to read the Extractors via DI.

To settle our dispute, copy the source code and modify it so it does not include the write to the file. If I am right it will take as long as before.

btw, can you post the generated ABAP, let’s try to find optimizations together…

Werner Daehn :de: (BOB member since 2004-12-17)

5

I heard that SAP is working to be able to read extractors via DI… (I had a typo-- the two other dfs are projectelementhierUP and projectelementhierLeft.

That will be win-win as some of the BW extractors are very cool and does provide delta capabilities – especially in LO…

I will send you the results with and w/o the file write… but, in debug… I could see that it took 22 secs to read SAP table into internal table… the first wrie to file took 4 mins and then the next chunk took 11 mins… Although, the write itself may not take a long time, I was trying to find a way not to produce flat files (as I mentioned, it will create a file on the ECC server and then we ftp over before the flow writes to database… when you process 10 or 20 million rows, this will be a bootleneck… I understand, with flat files, you could use bulk loaders but wondering about an option to write to the database…

Please note that there was a correction to this dataflow… instead of reading JEST… there is an outerjoin to read JSTO and JCDS etc., as far as I can see ABAP and Database selects are very fast and processing is in the internal table and writing to file…

I have attached the abap… Really appreciate your input…

not sure if file was attached… since it is not to big… i am copying into inline code…

[See post below for code]

Thanks

diarch :us: (BOB member since 2009-01-24)

6

pasting w/o smilies messing the code up…

REPORT ZAL_ABAP message-id 26 line-size 255
no standard page heading.

  • ZAWJESTJSTO.
    PARAMETER DOWNLOAD(1) DEFAULT ‘N’ lower case. "N-svr,Y-clnt
    PARAMETER EXECMODE(1) DEFAULT ‘B’ lower case. "D-dlg,B-btch
    PARAMETER OUT_DIR(48) DEFAULT "output file dir
    ‘/transfer/ds’ lower case.
    PARAMETER IN_DIR(48) DEFAULT "input file dir
    ‘/transfer/ds’ lower case.

PARAMETER $PARAM1(1) TYPE C.

PARAMETER $PARAM2(1) TYPE C.

PARAMETER $PARAM3 TYPE I.

PARAMETER P_DF_VK(40) DEFAULT "R/3 Dataflow object key
‘’ lower case.
PARAMETER P_DI_GEN(40) DEFAULT "DI version that generated ABAP
‘’ lower case.

*** Machine generated ABAP. Do not modify. ***
*** Copyright Business Objects Data Integration, Inc. ***
*

  • Date Time:
  • 01/22/09 19:20:00
  • SAP used for generated this ABAP:
  • Release: 700
  • Host : lhdevsap.d52.lilly.com
  • R3 Dataflow Name:
  • R3_ProjectStatus
  • ABAP program name in R3:
  • ZAWJESTJSTO
  • Generated ABAP file name:
  • e:/ds/abap/ProjectStatus.aba
    TABLES JSTO.
    TABLES JCDO.
    TABLES TJ03T.
    TABLES TJ20T.
    TABLES JEST.
    TABLES JCDS.

DATA: begin of ITAB5 occurs 0,
OBJECT_ID(22) TYPE C,
STATUS_CHGNR(3) TYPE N,
OBJECT_TYPE_ID(3) TYPE C,
OBJECT_TYPE_DESC(40) TYPE C,
STATUS_PROFILE(8) TYPE C,
STATUS_PROFILE_DESC(40) TYPE C,
STATUS_CHG_USER(12) TYPE C,
STATUS_CHG_DATE TYPE D,
STATUS_CHG_TIME TYPE T,
STATUS_CHG_TXN_CODE(4) TYPE C,
STATUS_CHG_TXN(20) TYPE C,
STATUS_CHG_OLD_STATUS(8) TYPE C,
STATUS_CHG_NEW_STATUS(8) TYPE C.
DATA: end of ITAB5.

DATA: begin of ITAB6 occurs 0,
OBJECT_ID(22) TYPE C,
OBJECT_STATUS(5) TYPE C,
OBJECT_STATUS_CHGNR(3) TYPE N,
OBJECT_STATUS_INACTIVE_IND(1) TYPE C,
OBJECT_STAT_CHG_USER(12) TYPE C,
OBJECT_STAT_CHG_DATE TYPE D,
OBJECT_STAT_CHG_TIME TYPE T,
OBJECT_STAT_CHG_TXN_CODE(4) TYPE C,
OBJECT_STAT_CHG_TXN(20) TYPE C,
OBJECT_STAT_CHG_INACTIVE_IND(1) TYPE C,
OBJECT_STAT_CHG_INDICATOR(1) TYPE C.
DATA: end of ITAB6.

DATA: begin of ITAB7 occurs 0,
OBJECT_ID(22) TYPE C,
OBJECT_STATUS(5) TYPE C,
STATUS_CHGNR(3) TYPE N,
OBJECT_STATUS_CHGNR(3) TYPE N,
OBJECT_TYPE_ID(3) TYPE C,
OBJECT_TYPE_DESC(40) TYPE C,
STATUS_PROFILE(8) TYPE C,
STATUS_PROFILE_DESC(40) TYPE C,
STATUS_CHG_USER(12) TYPE C,
STATUS_CHG_DATE TYPE D,
STATUS_CHG_TIME TYPE T,
STATUS_CHG_TXN_CODE(4) TYPE C,
STATUS_CHG_TXN(20) TYPE C,
STATUS_CHG_OLD_STATUS(8) TYPE C,
STATUS_CHG_NEW_STATUS(8) TYPE C,
OBJECT_STATUS_INACTIVE_IND(1) TYPE C,
OBJECT_STAT_CHG_USER(12) TYPE C,
OBJECT_STAT_CHG_DATE TYPE D,
OBJECT_STAT_CHG_TIME TYPE T,
OBJECT_STAT_CHG_TXN_CODE(4) TYPE C,
OBJECT_STAT_CHG_TXN(20) TYPE C,
OBJECT_STAT_CHG_INACTIVE_IND(1) TYPE C,
OBJECT_STAT_CHG_INDICATOR(1) TYPE C.
DATA: end of ITAB7.

data: append_flag(1) value ’ ',
cntbuf type i,
delimleng type i.

CONSTANTS C_DF_VK(40) VALUE ‘371’.
CONSTANTS C_DI_GEN(40) VALUE ‘12.0.0.1’.
DATA WARN_MSG(50).

start-of-selection.

PERFORM FORM3.
PERFORM FORM6.
PERFORM FORM7.
FREE ITAB5.
FREE ITAB6.
PERFORM FORM8.
FREE ITAB7.

end-of-selection.

CLEAR WARN_MSG.

IF NOT P_DF_VK IS INITIAL.
IF P_DF_VK <> C_DF_VK.
CONCATENATE ‘$$Warning$’
C_DF_VK
‘$’ INTO WARN_MSG.
ENDIF.
ENDIF.
IF NOT P_DI_GEN IS INITIAL.
IF P_DI_GEN <> C_DI_GEN.
IF WARN_MSG IS INITIAL.
CONCATENATE ‘$$Warning$$’
C_DI_GEN
INTO WARN_MSG.
ELSE.
CONCATENATE WARN_MSG
C_DI_GEN
INTO WARN_MSG.
ENDIF.
ENDIF.
ENDIF.

IF NOT WARN_MSG IS INITIAL.
IF EXECMODE = ‘D’.
WRITE WARN_MSG.
NEW-LINE.
ELSE.
MESSAGE S000 with WARN_MSG.
ENDIF.
ENDIF.

write ‘* Program Complete *’.
write ‘Copyright Business Objects Data Integration, Inc.’.

FORM FORM3.
DATA ALTMP1(22) TYPE C.
DATA ALTMP2(3) TYPE N.
DATA ALTMP3(3) TYPE C.
DATA ALTMP4(30) TYPE C.
DATA ALTMP5(8) TYPE C.
DATA ALTMP6(30) TYPE C.
DATA ALTMP7(12) TYPE C.
DATA ALTMP8 TYPE D.
DATA ALTMP9 TYPE T.
DATA ALTMP10(4) TYPE C.
DATA ALTMP11(20) TYPE C.
DATA ALTMP12(8) TYPE C.
DATA ALTMP13(8) TYPE C.
DATA ALOJF0 TYPE C.
DATA ALOJF1 TYPE C.

SELECT
OBJNR
OBTYP
STSMA
INTO CORRESPONDING FIELDS OF JSTO
FROM JSTO
WHERE ( ( ( OBJNR like ‘PR%’ )
OR ( OBJNR like ‘PD%’ ) )
OR ( OBJNR like ‘NV%’ ) ).
ALTMP1 = JSTO-OBJNR.
ALTMP3 = JSTO-OBTYP.
select single * from TJ03T where
OBTYP = JSTO-OBTYP and
SPRAS = $PARAM1 .
if sy-subrc = 0.
move TJ03T-TXT to ALTMP4.
else.
move $PARAM2 to ALTMP4.
endif.
ALTMP5 = JSTO-STSMA.
select single * from TJ20T where
STSMA = JSTO-STSMA and
SPRAS = $PARAM1 .
if sy-subrc = 0.
move TJ20T-TXT to ALTMP6.
else.
move $PARAM2 to ALTMP6.
endif.
ALOJF0 = ‘Y’.
ALOJF1 = ‘N’.
IF ( ALOJF0 = ‘Y’ ).

SELECT
CHGNR
USNAM
UDATE
UTIME
TCODE
CDTCODE
STSMA_OLD
STSMA_NEW
OBJNR
INTO CORRESPONDING FIELDS OF JCDO
FROM JCDO
WHERE ( OBJNR = JSTO-OBJNR ).
if JCDO-CHGNR = 0.
move $PARAM3 to ALTMP2.
else.
move JCDO-CHGNR to ALTMP2.
endif.
ALTMP7 = JCDO-USNAM.
ALTMP8 = JCDO-UDATE.
ALTMP9 = JCDO-UTIME.
ALTMP10 = JCDO-TCODE.
ALTMP11 = JCDO-CDTCODE.
ALTMP12 = JCDO-STSMA_OLD.
ALTMP13 = JCDO-STSMA_NEW.
ALOJF1 = ‘Y’.
move ALTMP1 to ITAB5-OBJECT_ID.
move ALTMP2 to ITAB5-STATUS_CHGNR.
move ALTMP3 to ITAB5-OBJECT_TYPE_ID.
move ALTMP4 to ITAB5-OBJECT_TYPE_DESC.
move ALTMP5 to ITAB5-STATUS_PROFILE.
move ALTMP6 to ITAB5-STATUS_PROFILE_DESC.
move ALTMP7 to ITAB5-STATUS_CHG_USER.
move ALTMP8 to ITAB5-STATUS_CHG_DATE.
move ALTMP9 to ITAB5-STATUS_CHG_TIME.
move ALTMP10 to ITAB5-STATUS_CHG_TXN_CODE.
move ALTMP11 to ITAB5-STATUS_CHG_TXN.
move ALTMP12 to ITAB5-STATUS_CHG_OLD_STATUS.
move ALTMP13 to ITAB5-STATUS_CHG_NEW_STATUS.
append ITAB5.
ENDSELECT.
ENDIF.
IF ( ALOJF1 = ‘N’ ).
clear JCDO-CHGNR.
clear JCDO-USNAM.
clear JCDO-UDATE.
clear JCDO-UTIME.
clear JCDO-TCODE.
clear JCDO-CDTCODE.
clear JCDO-STSMA_OLD.
clear JCDO-STSMA_NEW.
clear JCDO-OBJNR.
if JCDO-CHGNR = 0.
move $PARAM3 to ALTMP2.
else.
move JCDO-CHGNR to ALTMP2.
endif.
clear ALTMP7.
clear ALTMP8.
clear ALTMP9.
clear ALTMP10.
clear ALTMP11.
clear ALTMP12.
clear ALTMP13.
move ALTMP1 to ITAB5-OBJECT_ID.
move ALTMP2 to ITAB5-STATUS_CHGNR.
move ALTMP3 to ITAB5-OBJECT_TYPE_ID.
move ALTMP4 to ITAB5-OBJECT_TYPE_DESC.
move ALTMP5 to ITAB5-STATUS_PROFILE.
move ALTMP6 to ITAB5-STATUS_PROFILE_DESC.
move ALTMP7 to ITAB5-STATUS_CHG_USER.
move ALTMP8 to ITAB5-STATUS_CHG_DATE.
move ALTMP9 to ITAB5-STATUS_CHG_TIME.
move ALTMP10 to ITAB5-STATUS_CHG_TXN_CODE.
move ALTMP11 to ITAB5-STATUS_CHG_TXN.
move ALTMP12 to ITAB5-STATUS_CHG_OLD_STATUS.
move ALTMP13 to ITAB5-STATUS_CHG_NEW_STATUS.
append ITAB5.
ENDIF.
ENDSELECT.
ENDFORM.

FORM FORM6.
DATA ALTMP14(22) TYPE C.
DATA ALTMP15(5) TYPE C.
DATA ALTMP16(3) TYPE N.
DATA ALTMP17(1) TYPE C.
DATA ALTMP18(12) TYPE C.
DATA ALTMP19 TYPE D.
DATA ALTMP20 TYPE T.
DATA ALTMP21(4) TYPE C.
DATA ALTMP22(20) TYPE C.
DATA ALTMP23(1) TYPE C.
DATA ALTMP24(1) TYPE C.
DATA ALOJF0 TYPE C.
DATA ALOJF1 TYPE C.

SELECT
OBJNR
STAT
INACT
INTO CORRESPONDING FIELDS OF JEST
FROM JEST
WHERE ( ( ( OBJNR like ‘PR%’ )
OR ( OBJNR like ‘PD%’ ) )
OR ( OBJNR like ‘NV%’ ) ).
ALTMP14 = JEST-OBJNR.
ALTMP15 = JEST-STAT.
ALTMP17 = JEST-INACT.
ALOJF0 = ‘Y’.
ALOJF1 = ‘N’.
IF ( ALOJF0 = ‘Y’ ).

SELECT
CHGNR
USNAM
UDATE
UTIME
TCODE
CDTCODE
INACT
CHIND
OBJNR
STAT
INTO CORRESPONDING FIELDS OF JCDS
FROM JCDS
WHERE ( ( STAT = JEST-STAT )
AND ( OBJNR = JEST-OBJNR ) ).
if JCDS-CHGNR = 0.
move $PARAM3 to ALTMP16.
else.
move JCDS-CHGNR to ALTMP16.
endif.
ALTMP18 = JCDS-USNAM.
ALTMP19 = JCDS-UDATE.
ALTMP20 = JCDS-UTIME.
ALTMP21 = JCDS-TCODE.
ALTMP22 = JCDS-CDTCODE.
ALTMP23 = JCDS-INACT.
ALTMP24 = JCDS-CHIND.
ALOJF1 = ‘Y’.
move ALTMP14 to ITAB6-OBJECT_ID.
move ALTMP15 to ITAB6-OBJECT_STATUS.
move ALTMP16 to ITAB6-OBJECT_STATUS_CHGNR.
move ALTMP17 to ITAB6-OBJECT_STATUS_INACTIVE_IND.
move ALTMP18 to ITAB6-OBJECT_STAT_CHG_USER.
move ALTMP19 to ITAB6-OBJECT_STAT_CHG_DATE.
move ALTMP20 to ITAB6-OBJECT_STAT_CHG_TIME.
move ALTMP21 to ITAB6-OBJECT_STAT_CHG_TXN_CODE.
move ALTMP22 to ITAB6-OBJECT_STAT_CHG_TXN.
move ALTMP23 to ITAB6-OBJECT_STAT_CHG_INACTIVE_IND.
move ALTMP24 to ITAB6-OBJECT_STAT_CHG_INDICATOR.
append ITAB6.
ENDSELECT.
ENDIF.
IF ( ALOJF1 = ‘N’ ).
clear JCDS-CHGNR.
clear JCDS-USNAM.
clear JCDS-UDATE.
clear JCDS-UTIME.
clear JCDS-TCODE.
clear JCDS-CDTCODE.
clear JCDS-INACT.
clear JCDS-CHIND.
clear JCDS-OBJNR.
clear JCDS-STAT.
if JCDS-CHGNR = 0.
move $PARAM3 to ALTMP16.
else.
move JCDS-CHGNR to ALTMP16.
endif.
clear ALTMP18.
clear ALTMP19.
clear ALTMP20.
clear ALTMP21.
clear ALTMP22.
clear ALTMP23.
clear ALTMP24.
move ALTMP14 to ITAB6-OBJECT_ID.
move ALTMP15 to ITAB6-OBJECT_STATUS.
move ALTMP16 to ITAB6-OBJECT_STATUS_CHGNR.
move ALTMP17 to ITAB6-OBJECT_STATUS_INACTIVE_IND.
move ALTMP18 to ITAB6-OBJECT_STAT_CHG_USER.
move ALTMP19 to ITAB6-OBJECT_STAT_CHG_DATE.
move ALTMP20 to ITAB6-OBJECT_STAT_CHG_TIME.
move ALTMP21 to ITAB6-OBJECT_STAT_CHG_TXN_CODE.
move ALTMP22 to ITAB6-OBJECT_STAT_CHG_TXN.
move ALTMP23 to ITAB6-OBJECT_STAT_CHG_INACTIVE_IND.
move ALTMP24 to ITAB6-OBJECT_STAT_CHG_INDICATOR.
append ITAB6.
ENDIF.
ENDSELECT.
ENDFORM.

FORM FORM7.
DATA ALTMP25(22) TYPE C.
DATA ALTMP26(5) TYPE C.
DATA ALTMP27(3) TYPE N.
DATA ALTMP28(3) TYPE N.
DATA ALTMP29(3) TYPE C.
DATA ALTMP30(40) TYPE C.
DATA ALTMP31(8) TYPE C.
DATA ALTMP32(40) TYPE C.
DATA ALTMP33(12) TYPE C.
DATA ALTMP34 TYPE D.
DATA ALTMP35 TYPE T.
DATA ALTMP36(4) TYPE C.
DATA ALTMP37(20) TYPE C.
DATA ALTMP38(8) TYPE C.
DATA ALTMP39(8) TYPE C.
DATA ALTMP40(1) TYPE C.
DATA ALTMP41(12) TYPE C.
DATA ALTMP42 TYPE D.
DATA ALTMP43 TYPE T.
DATA ALTMP44(4) TYPE C.
DATA ALTMP45(20) TYPE C.
DATA ALTMP46(1) TYPE C.
DATA ALTMP47(1) TYPE C.

LOOP AT ITAB5.
ALTMP27 = ITAB5-STATUS_CHGNR.
ALTMP29 = ITAB5-OBJECT_TYPE_ID.
ALTMP30 = ITAB5-OBJECT_TYPE_DESC.
ALTMP31 = ITAB5-STATUS_PROFILE.
ALTMP32 = ITAB5-STATUS_PROFILE_DESC.
ALTMP33 = ITAB5-STATUS_CHG_USER.
ALTMP34 = ITAB5-STATUS_CHG_DATE.
ALTMP35 = ITAB5-STATUS_CHG_TIME.
ALTMP36 = ITAB5-STATUS_CHG_TXN_CODE.
ALTMP37 = ITAB5-STATUS_CHG_TXN.
ALTMP38 = ITAB5-STATUS_CHG_OLD_STATUS.
ALTMP39 = ITAB5-STATUS_CHG_NEW_STATUS.
LOOP AT ITAB6 WHERE ( OBJECT_ID = ITAB5-OBJECT_ID ).
ALTMP25 = ITAB6-OBJECT_ID.
ALTMP26 = ITAB6-OBJECT_STATUS.
ALTMP28 = ITAB6-OBJECT_STATUS_CHGNR.
ALTMP40 = ITAB6-OBJECT_STATUS_INACTIVE_IND.
ALTMP41 = ITAB6-OBJECT_STAT_CHG_USER.
ALTMP42 = ITAB6-OBJECT_STAT_CHG_DATE.
ALTMP43 = ITAB6-OBJECT_STAT_CHG_TIME.
ALTMP44 = ITAB6-OBJECT_STAT_CHG_TXN_CODE.
ALTMP45 = ITAB6-OBJECT_STAT_CHG_TXN.
ALTMP46 = ITAB6-OBJECT_STAT_CHG_INACTIVE_IND.
ALTMP47 = ITAB6-OBJECT_STAT_CHG_INDICATOR.
move ALTMP25 to ITAB7-OBJECT_ID.
move ALTMP26 to ITAB7-OBJECT_STATUS.
move ALTMP27 to ITAB7-STATUS_CHGNR.
move ALTMP28 to ITAB7-OBJECT_STATUS_CHGNR.
move ALTMP29 to ITAB7-OBJECT_TYPE_ID.
move ALTMP30 to ITAB7-OBJECT_TYPE_DESC.
move ALTMP31 to ITAB7-STATUS_PROFILE.
move ALTMP32 to ITAB7-STATUS_PROFILE_DESC.
move ALTMP33 to ITAB7-STATUS_CHG_USER.
move ALTMP34 to ITAB7-STATUS_CHG_DATE.
move ALTMP35 to ITAB7-STATUS_CHG_TIME.
move ALTMP36 to ITAB7-STATUS_CHG_TXN_CODE.
move ALTMP37 to ITAB7-STATUS_CHG_TXN.
move ALTMP38 to ITAB7-STATUS_CHG_OLD_STATUS.
move ALTMP39 to ITAB7-STATUS_CHG_NEW_STATUS.
move ALTMP40 to ITAB7-OBJECT_STATUS_INACTIVE_IND.
move ALTMP41 to ITAB7-OBJECT_STAT_CHG_USER.
move ALTMP42 to ITAB7-OBJECT_STAT_CHG_DATE.
move ALTMP43 to ITAB7-OBJECT_STAT_CHG_TIME.
move ALTMP44 to ITAB7-OBJECT_STAT_CHG_TXN_CODE.
move ALTMP45 to ITAB7-OBJECT_STAT_CHG_TXN.
move ALTMP46 to ITAB7-OBJECT_STAT_CHG_INACTIVE_IND.
move ALTMP47 to ITAB7-OBJECT_STAT_CHG_INDICATOR.
append ITAB7.
cntbuf = cntbuf + 1.
if download = ‘N’.
if cntbuf > 5000.
perform FORM8.
clear cntbuf.
refresh ITAB7.
append_flag = ‘A’.
endif.
endif.
ENDLOOP.
ENDLOOP.
ENDFORM.

FORM FORM8.
data: outfile(128), ldfile(50).
ldfile = ‘ProjectStatus.dat’.
concatenate out_dir ldfile into outfile
separated by ‘/’.
data dlmtlen type i value ‘1’.
data ht(1) type x value ‘7F’.
data return_code type i.
perform write_delimited_file
tables ITAB7
using outfile
append_flag
ht
dlmtlen
download
changing return_code.

case return_code.
when 1.
IF EXECMODE = ‘D’.
WRITE: /5 ‘No line selected’.
ELSE.
MESSAGE E098.
ENDIF.
when 2.
IF EXECMODE = ‘D’.
WRITE: /5 'Open File Error – ', 25 OUTFILE.
ELSE.
MESSAGE E107 WITH OUTFILE.
ENDIF.
when 3.
IF EXECMODE = ‘D’.
WRITE: /5 'Data exceed length limit (8192) '.
ELSE.
MESSAGE E000 WITH
'Data exceed length limit (8192) '.
ENDIF.
when 4.
IF EXECMODE = ‘D’.
WRITE: /5 ‘Call function WS_DOWNLOAD error’.
ELSE.
MESSAGE E000 WITH
‘Call function WS_DOWNLOAD error’.
ENDIF.
endcase.
ENDFORM.

FORM SUBSTRING USING SRC BEG LEN CHANGING RET.

DATA: VA1 TYPE I.
DATA: VA2 TYPE I.
DATA: VA3 TYPE I.

VA3 = STRLEN( SRC ).

IF BEG = 0. VA1 = 0.
ELSE.
IF BEG < 0.
VA1 = VA3 + BEG.
IF VA1 < 0. VA1 = 0.
ENDIF.
ELSE. VA1 = BEG - 1.
ENDIF.
ENDIF.

IF LEN < 0. VA2 = 0.
ELSE. VA2 = VA3 - VA1.
ENDIF.

IF VA2 > LEN. VA2 = LEN.
ENDIF.

IF VA2 < 1. MOVE ‘’ TO RET.
ELSE. MOVE SRC+VA1(VA2) TO RET.
ENDIF.

ENDFORM.

form write_delimited_file
tables datatab
using file
append
delimit
dlength
dwnload
changing rc.

data: type1,
appd(1),
temp(32),
time1(8),
date1(10),
output(8192),
rcount type i,
offset type i,
tablen type i,
maxlen type i value ‘8192’.

data: begin of clientab occurs 0,
output(8192),
end of clientab.

field-symbols: .
field-symbols .
data delim2(16).
data l_filename type string.

appd = append.
if appd is not initial.
appd = ‘X’.
endif.
move file to l_filename.
describe table datatab lines tablen.

if dwnload = ‘Y’.
clear clientab. refresh clientab.
rcount = 0.
else.
if appd = space.
open dataset file for output in text mode ENCODING
DEFAULT.
else.
open dataset file for appending in text mode ENCODING
DEFAULT.
endif.
if sy-subrc <> 0.
rc = 2. exit.
endif.
endif.

loop at datatab.
clear: tablen, offset, output.
do.
assign component sy-index of
structure datatab to .
if sy-subrc <> 0. exit. endif.
if sy-index > 1.
assign delimit(dlength) TO CASTING TYPE C.
delim2 = .
write delim2(dlength) to output+offset(dlength).
add dlength to offset.
endif.

  describe field <f> type type1.                    
                                                    
  if type1 = 'I' or type1 = 'N'.                    
      type1 = 'P'.                                  
  endif.                                            
                                                    
  case type1.                                       
    when 'D'.                                       
      if <f> = '00000000'.                          
         <f> = ' '.                                 
      else.                                         
         move <f> to time1.                         
         assign time1 to <f>.                       
      endif.                                        
    when 'F'.                                       
      if <f> = '0.0'.                               
        temp = '0.0'.                               
      else.                                         
         write <f> to temp exponent 0.              
      endif.                                        
      condense temp no-gaps.                        
      translate temp using ',.'.                    
      assign temp to <f>.                           
    when 'P'.                                       
      if <f> < 0.                                   
         write '-' to output+offset(1).             
         add 1 to offset.                           
         <f> = <f> * ( -1 ).                        
      endif.                                        
      move <f> to temp.                             
      condense temp no-gaps.                        
      translate temp using ',.'.                    
      assign temp to <f>.                           
  endcase.                                          
                                                    
  sy-fdpos = strlen( <f> ).                         
                                                    
  tablen = offset + sy-fdpos.                       
  if tablen > maxlen.                               
     rc = 3. exit.                                  
  endif.                                            
  write <f> to output+offset(sy-fdpos).             
  add sy-fdpos to offset.                           
enddo.                                              
                                                    
if dwnload = 'Y'.                                   
   clientab-output = output.                        
   append clientab.                                 
   rcount = rcount + 1.                             
   if rcount >= 50.                                 
      SY-BATCH = SPACE.                             
      CALL FUNCTION 'GUI_DOWNLOAD'                  
        EXPORTING                                   
          FILENAME = l_filename                     
          FILETYPE = 'ASC'                          
          APPEND   = appd                           
          WRITE_FIELD_SEPARATOR = 'X'               

  •        IMPORTING                                  

  •          FILELENGTH =                             
      TABLES                                      
        DATA_TAB = clientab                       
      EXCEPTIONS                                  
        OTHERS = 1.                               
    if sy-subrc <> 0.                             
       rc = 4.                                    
    endif.                                        
    clear clientab. refresh clientab.             
    rcount = 0. appd = 'A'.                       
 endif.

    else.
    transfer output to file.
    endif.
    endloop.

    if dwnload = ‘Y’.
    SY-BATCH = SPACE.
    CALL FUNCTION ‘GUI_DOWNLOAD’
    EXPORTING
    FILENAME = l_filename
    FILETYPE = ‘ASC’
    APPEND = appd
    WRITE_FIELD_SEPARATOR = ‘X’

  •     IMPORTING                                     

  •       FILELENGTH =                                
   TABLES                                         
     DATA_TAB = clientab                          
   EXCEPTIONS                                     
     OTHERS = 1.                                  
    if sy-subrc <> 0.                             
       rc = 4.                                    
    endif.

    else.
    close dataset file.
    endif.
    endform.

diarch :us: (BOB member since 2009-01-24)

7

:mrgreen:

The interesting part is FORM7:


LOOP AT ITAB5. 
  ALTMP27 = ITAB5-STATUS_CHGNR. 
  ....
  . 
  LOOP AT ITAB6 WHERE ( OBJECT_ID = ITAB5-OBJECT_ID ). 
    ALTMP25 = ITAB6-OBJECT_ID. 
    .... 
    append ITAB7. 
    cntbuf = cntbuf + 1. 
    if download = 'N'. 
      if cntbuf > 5000. 
        perform FORM8. 
        clear cntbuf. 
        refresh ITAB7. 
        append_flag = 'A'. 
      endif. 
    endif. 
  ENDLOOP. 
ENDLOOP.

So for each ITAB5, find all matching ITAB6 records and save the results in ITAB7. Once 5000 rows have been appended, write the data to the file via FORM8.

As the ITAB is indexed, I would think that to be fast. I would rather expect one of the FORMs building those ITAB5&6 to be the problem but then it would not match your observation of writing the file. At the time ITAB5&6 are built no file is written. I am confused now.

Werner Daehn :de: (BOB member since 2004-12-17)

8

I have checked the ABAP itself. I cannot see anything obviously wrong about it but need you help to validate it.

We have a first

SELECT 
OBJNR 
OBTYP 
STSMA 
INTO CORRESPONDING FIELDS OF JSTO 
FROM JSTO 
WHERE ( ( ( OBJNR like 'PR%' ) 
OR ( OBJNR like 'PD%' ) ) 
OR ( OBJNR like 'NV%' ) ).

that reads a good portion of the JSTO table into an ITAB. Will take a while but is done just once. No index required, it probably will not be used by the database optimizer anyway.

For each of these records you do a

select single * from TJ03T where 
OBTYP = JSTO-OBTYP and 
SPRAS = $PARAM1 .

and


select single * from TJ20T where 
STSMA = JSTO-STSMA and 
SPRAS = $PARAM1 .

I am pretty sure these are the primary keys of this table. So it should not take that long. However, rowcounts would be interesting. If the JSTO select returns billions of rows, the many select singles will take a while even if there is a primary key. Can you get me the row counts via /nse16 for the three tables with the same constant filters? How many JSTO rows do we have matching the three filter conditions? How many english TJ20T and TJ03T are there?..
Actually no, I don’t need that. Those two tables are tiny anyway, aren’t they? Please change the two lookups in the R/3 dataflow to pre_load_cache mode. Minor optimization.
So just the rowcounts for the JSTO with filter please.

And then there is a

SELECT 
CHGNR 
USNAM 
UDATE 
UTIME 
TCODE 
CDTCODE 
STSMA_OLD 
STSMA_NEW 
OBJNR 
INTO CORRESPONDING FIELDS OF JCDO 
FROM JCDO 
WHERE ( OBJNR = JSTO-OBJNR ).

So the JCDO table has to have an index with OBJNR as its first column. Does it?

In FORM6 we have a


SELECT 
OBJNR 
STAT 
INACT 
INTO CORRESPONDING FIELDS OF JEST 
FROM JEST 
WHERE ( ( ( OBJNR like 'PR%' ) 
OR ( OBJNR like 'PD%' ) ) 
OR ( OBJNR like 'NV%' ) ).

Same story as above. One select over a big amount of data. Will take a few minutes but is done just once. The number of rows matching this filter would be interesting as well.


SELECT 
CHGNR 
USNAM 
UDATE 
UTIME 
TCODE 
CDTCODE 
INACT 
CHIND 
OBJNR 
STAT 
INTO CORRESPONDING FIELDS OF JCDS 
FROM JCDS 
WHERE ( ( STAT = JEST-STAT ) 
AND ( OBJNR = JEST-OBJNR ) ).

requires an index on STAT and OBJNR. Those two columns have to be the first two of an index - order does not matter. If not, at least an index with OBJNR as first column should exist.

I wonder if a different execution path would work better in your case. If you have an idea, we just need to change join ranks and cache settings in the source tables of the R/3 dataflow to get a different ABAP. Problem with the RapidMarts is, they cannot be optimized for everybody. Some companies have 10 materials in MARA and sell those millions of times. Others sell atomic powerplants where each order alone has millions of materials as line items. So we need to tune them. Luckily, with DI you can do that, it requires experience however.

Werner Daehn :de: (BOB member since 2004-12-17)

9

First of all… Thanks very much for your time and effort… very much appreciated… The problem is definitely in the processing not writing out to file… So, you are absolutely right. ITAB5 and 6 take a long time.

I am trying to do runtime analyisis (SE30)

The ProjElementHierUp and Left are still running since 10 am (more than 24 hrs) and same with projectstatus… I have been doing similar analysis like you are and here are the info you have asked… Again… thanks for your invaluable input…

So just the rowcounts for the JSTO with filter please.

1,160676 (for objnr like ‘PR%, PD% and NV%’

NO index on JSTO but as you said… does not take any time at all to read

  1. TJ03T - 1341 rows

  2. TJ20T - 385 rows

  3. NO INDEX on JCDO (need to talk to DBA why there is not one on OBJNR? MANDT, OBJNR and CHNGR are primary keys)
    JCDO has only 18 rows???

5)JEST has inndexes (MANDT, STAT, INACT) created by SAP - non-unique index.
Total rows for objnr like 'PR*, PD* and NV*) is 4776727

Did change cache settings – minor improvement.

I will explore changing ranks…

Thanks again and please let me know if you have any other suggestions…

diarch :us: (BOB member since 2009-01-24)

10

Forgot about the MANDT field - if that is the first column of an index it is still okay, SAP will add that filter one the client-number anyway.

Actually I am stunned it takes that long. The first one reads 1 million records into ITAB5, the second 4 million rows into ITAB6 and then a join between the two ITABs is made - no idea how long that would take but should not be longer than an hour if that.

That caching the lookups did help somewhat indicates we are one the right track, I feel. And that leads directly to the JCDO table. Okay, it has just 18 records - that’s not much. But for we execute that select against that table 1 million times. The database will have cached that table for sure and return the result of that select from the database cache but still, we execute that 1 million times. One million times 1ms is still a significant time - and who knows how long it actually takes, maybe 10ms?

Anyway, the first thing I would do is changing the cache flag of the JCDO table to yes. The ABAP we generate should read the entire JCDO table - all 18 rows - into an ITAB of its own and perform the join against that. The second question I ask myself is if that JCDO table has valuable information at all. I mean, out of 1 million rows just 18 have this additional information - is it even worth it??? (Production has a few rows only as well, does it?) Not to mention a primary key is not supported by an index??? Very odd. Anyway.

The ProjectElemHierLeft.aba you emailed is way simpler, it is reading the data into ITABs and joining in memory. I would not think it takes longer than 10 minutes regardeless of the volumes!?! Does that mean we have a performance problem with the

read table ITAB_3 with key PSPNR = xxxx

???

What could be wrong with that?!???

Werner Daehn :de: (BOB member since 2004-12-17)