Loop Internal Table Performance

[Former Member]

Hi I am from BW and not much familiar with ABAP. I have written the following code in BW. I have around 1.5 million records coming into BW and the code I have written needs to go through 1.5 million times. Hence the data load is taking long time. I would like someone to please go through the code below and suggest if any for performance improvement.

data: i_datapackage like DATA_PACKAGE occurs 0 with header line,

i_index like sy-tabix,

j_index like sy-tabix,

i_employee like /bi0/memployee occurs 0 with header line,

i_begda like /bi0/memployee occurs 0 with header line.

prev_job like i_employee-job,

prev_position like i_employee-hrposition,

curr_job like i_employee-job,

curr_position like i_employee-hrposition,

job_datefrom like i_employee-datefrom,

position_datefrom like i_employee-datefrom,

prejob_datefrom like i_employee-datefrom,

prepos_datefrom like i_employee-datefrom,

prejob_dateto like i_employee-datefrom,

prepos_dateto like i_employee-datefrom.

i_datapackage[] = DATA_PACKAGE[].

sort i_datapackage by employee calmonth ascending.

select * from /bi0/memployee into table i_employee.

sort i_employee by employee dateto descending.

i_begda[] = i_employee[].

sort i_begda by employee datefrom ascending.

delete adjacent duplicates from i_employee comparing employee.

loop at i_datapackage.

*collecting current job,position, previous job, position

read table i_employee with key employee = i_datapackage-employee

dateto ='99991231'.

if sy-subrc = 0.

curr_job = i_employee-job.

curr_position = i_employee-hrposition.

endif.

read table i_begda with key employee = i_datapackage-employee.

if sy-subrc = 0.

prev_job = i_begda-/bic/zhr_pjob.

prev_position = i_begda-/bic/zhr_ppost.

endif.

*end of collecting current job and position

sort i_begda by employee datefrom descending.

read table i_begda with key employee = i_datapackage-employee

binary search.

if sy-subrc = 0.

i_index = sy-tabix.

endif.

read table i_begda index i_index.

if i_begda-employee ne i_datapackage-employee.

exit.

endif.

• code for populating service award tenure

if i_begda-/bic/zhr_srvdt is not initial.

i_datapackage-/bic/zhr_hrtnr = sy-datum+0(4) -

i_begda-/BIC/ZHR_SRVDt+0(4).

if sy-datum4(4) lt i_begda-/bic/zhr_srvdt4(4).

i_datapackage-/bic/zhr_hrtnr = i_datapackage-/bic/zhr_hrtnr - 1.

endif.

endif.

• code for current hire tenure

if i_begda-/bic/zhr_curdt is not initial.

i_datapackage-/bic/zhr_crtnr = sy-datum+0(4) -

i_begda-/BIC/ZHR_CURDT+0(4).

if sy-datum4(4) lt i_begda-/BIC/ZHR_CURDT4(4).

i_datapackage-/BIC/ZHR_CRTNR = i_datapackage-/bic/zhr_crtnr - 1.

endif.

endif.

• code for current supervisor tenure

if i_begda-/bic/zhr_csbdt is not initial.

i_datapackage-/bic/ZHR_CSTNR = sy-datum+0(4) -

i_begda-/BIC/ZHR_CSBDT+0(4).

if sy-datum4(4) lt i_begda-/BIC/ZHR_CSBDT4(4).

i_datapackage-/bic/ZHR_CSTNR = i_datapackage-/bic/ZHR_CSTNR - 1.

endif.

endif.

• code for previous supervisor tenure

if i_begda-/bic/zhr_psbdt is not initial.

i_datapackage-/bic/ZHR_PSTNR = sy-datum+0(4) -

i_begda-/BIC/ZHR_PSBDT+0(4).

if sy-datum4(4) lt i_begda-/BIC/ZHR_PSBDT4(4).

i_datapackage-/bic/ZHR_PSTNR = i_datapackage-/bic/ZHR_PSTNR - 1.

endif.

endif.

• Code for populating Years in current job

• and current position

sort i_begda by employee datefrom ascending.

clear j_index.

read table i_begda with key employee = i_datapackage-employee.

if sy-subrc = 0.

j_index = sy-tabix.

endif.

loop at i_begda from j_index.

if i_begda-employee ne i_datapackage-employee.

exit.

elseif ( i_begda-job eq curr_job ) and

( curr_job is not initial ).

job_datefrom = i_begda-datefrom.

prejob_dateto = i_begda-datefrom - 1.

exit.

endif.

endloop.

loop at i_begda from j_index.

if i_begda-employee ne i_datapackage-employee.

exit.

elseif ( i_begda-hrposition eq curr_position ) and

( curr_position is not initial ).

position_datefrom = i_begda-datefrom.

prepos_dateto = i_begda-datefrom - 1.

exit.

endif.

endloop.

loop at i_begda from j_index.

if i_begda-employee ne i_datapackage-employee.

exit.

elseif ( i_begda-job eq prev_job ) and

( prev_job is not initial ) and

( i_begda-datefrom ne '10000101').

prejob_datefrom = i_begda-datefrom.

exit.

endif.

endloop.

loop at i_begda from j_index.

if i_begda-employee ne i_datapackage-employee.

exit.

elseif ( i_begda-hrposition eq prev_position ) and

( prev_position is not initial ) and

( i_begda-datefrom ne '10000101').

prepos_datefrom = i_begda-datefrom.

exit.

endif.

endloop.

i_datapackage-/bic/zhr_cjtnr = sy-datum+0(4) -

job_datefrom+0(4).

if sy-datum4(4) lt job_datefrom4(4).

i_datapackage-/bic/zhr_cjtnr = i_datapackage-/bic/zhr_cjtnr - 1.

endif.

if position_datefrom+0(4) is not initial.

i_datapackage-/bic/zhr_cptnr = sy-datum+0(4) -

position_datefrom+0(4).

if sy-datum4(4) lt position_datefrom4(4).

i_datapackage-/bic/zhr_cptnr = i_datapackage-/bic/zhr_cptnr - 1.

endif.

endif.

if prejob_datefrom is not initial.

i_datapackage-/bic/zhr_pjtnr = prejob_dateto+0(4) -

prejob_datefrom+0(4).

if prejob_dateto4(4) lt prejob_datefrom4(4).

i_datapackage-/bic/zhr_pjtnr = i_datapackage-/bic/zhr_pjtnr - 1.

endif.

endif.

if prepos_datefrom is not initial.

i_datapackage-/bic/zhr_pptnr = prepos_dateto+0(4) -

prepos_datefrom+0(4).

if prepos_dateto4(4) lt prepos_datefrom4(4).

i_datapackage-/bic/zhr_pptnr = i_datapackage-/bic/zhr_pptnr - 1.

endif.

endif.

modify i_datapackage transporting /bic/zhr_hrtnr /bic/zhr_crtnr

/bic/zhr_cstnr /bic/ZHR_PSTNR /bic/zhr_cjtnr /bic/zhr_cptnr

/bic/zhr_pjtnr /bic/zhr_pptnr.

endloop.

DATA_PACKAGE[] = i_datapackage[].

Thanks.

[Former Member]

you have nested loops in your code. you can try to avoid that. and another thing is you are reading internal tables inside the loop. you can do one of the following two thigns

1. remove the <b>sort</b> and just do <b>read</b>

2. have the <b>sort</b> by the key you are using in the <b>read</b> and when you do a <b>read</b>, also include the addition <b>binary search</b>. this should surely increase the performance.

Thanks.

Message was edited by:

mg s

[Former Member]

hi,

why u dont use a sorted table ?

data : i_employee type sortes table of /bi0/memployee .....

w_employee like line of i_employee.

( without occurs an without header line )

loop at i_employee into w_employee.

endloop.

Better

loop at i_empolyee assigning <employee>.

hans

[Former Member]

Hi kiran,

1. You have used sort statments in side loop statement. It will be performance

issue.

Sort all the internal tables outside of the loop.

2. Use field symbol instead of modify statement. Field symbol references memory of a original field. It will not use any memory.

3. Read all the internal tabes with binary search

Regards,

Kishore

[Former Member]

i_datapackage[] = DATA_PACKAGE[].

i_begda[] = i_employee[].

How larger are these internal tables ?

If large, then

> sort i_begda by employee datefrom descending.

> read table i_begda with key employee = i_datapackage-employee binary search.

the resorting and

the nested loops

=> will kill your performance

Please reconsider the whole logic, I have only some hints

+ Binary search does not help anything if you have to sort everytime you use it.

+ Never use a sort of a larger table inside a larger loop, it is always avoidable!

+ Check whether you can break down the big piece into smaller one, try to

do the break down in the database already to save memory

=> Please provide your code in better layout, and explain what you actually want

to do.

Siegfried