Performance problem with an abap code

[Former Member]

Hi all,

I am having a performnance problem with the code below. Any recomendations ?

SELECT rnr FROM rsseldone INTO w_psarqst-low

WHERE seldate BETWEEN p_date-low AND p_date-high

AND source EQ p_source.

APPEND w_psarqst.

ENDSELECT.

• Fill-up the internal table from activity item psa for given interval.

LOOP AT w_psarqst.

SELECT guid item_guid created_at item_changed_at FROM (l_psa) INTO

(w_act_psa-l_guid, w_act_psa-l_itmguid, w_act_psa-l_cr_date, w_act_psa-l_ch_date)

WHERE request = w_psarqst-low.

APPEND w_act_psa.

ENDSELECT.

LOOP AT w_act_psa.

IF w_act_psa-l_ch_date = '00000000'.

w_act_psa-l_ch_date = w_act_psa-l_cr_date.

ENDIF.

• update active table.

TRY.

UPDATE /bic/azsc_d00300

SET /bic/zsc_date = w_act_psa-l_ch_date

WHERE /bic/zsc_acgui = w_act_psa-l_guid

AND /bic/zsc_itmgu = w_act_psa-l_itmguid.

CATCH cx_sy_dynamic_osql_error.

MESSAGE `Error in update!` TYPE 'I'.

WRITE:/'Error in update!'.

COMMIT WORK.

ENDTRY.

ENDLOOP.

ENDLOOP.

WRITE:/'Update finished successfully.'.

[Former Member]

Hi,

Instead of the Select and Endselect, create internal table and dump the reocrds before the LOOP statment.

LOOP AT w_psarqst.
Read table (Select Endselct internal talbe).

then instead of the inner loop, try to change the Read statment, LOOP with in the loop will take maximun time.
Endloop.

Regards

Sudheer

[Former Member]

Thanks guys, I will try and let you know...I already assigned you guys points.

Message was edited by:

Mike Curtis

[Former Member]

Sudheer,

Could you tell me an alternative instead of inner loop? I am new on abap

Thanks

[Former Member]

Hi,

The performance problem is due to the Select .. End Select statements and also due to the Select Statements within the Loop . Instead os appending a table in a Select .. End Select statements select the values satisfying a given criterior into a table like

DATA : Begin of w_psarqst occurs 0,

rnr LIKE rsseldone-rnr,

END of w_psarqst

SELECT rnr FROM rsseldone INTO TABLE w_psarqst

WHERE seldate BETWEEN p_date-low AND p_date-high

AND source EQ p_source.

Regards,

Sowmya

[amit_khare]

Hi,

what I got from these statements, I can suggest to try following.

SELECT rnr FROM rsseldone INTO w_psarqst-low

WHERE seldate <b> IN p_date</b>

AND source EQ p_source.

APPEND w_psarqst.

ENDSELECT.

• assuming w_psarqst with following fields, LOW, HIGH, OPTION and SIGN.

<b>

SELECT guid item_guid created_at item_changed_at FROM (l_psa) INTO

table wa_act_psa WHERE request in w_psarqst.</b>

Regards,

Amit

[Former Member]

Remove ur first loop and write the below select and try

* Fill-up the internal table from activity item psa for given interval.

if not w_psarqst[] is initial.
SELECT guid item_guid 
             created_at 
             item_changed_at 
            FROM (l_psa) 
            INTO corresponding fields of table w_act_psa
            for all entries in w_psarqst 
            WHERE request = w_psarqst-low.
endif.

LOOP AT w_act_psa.

IF w_act_psa-l_ch_date = '00000000'.
w_act_psa-l_ch_date = w_act_psa-l_cr_date.
ENDIF.
* update active table.
TRY.
UPDATE /bic/azsc_d00300
SET /bic/zsc_date = w_act_psa-l_ch_date
WHERE /bic/zsc_acgui = w_act_psa-l_guid
AND /bic/zsc_itmgu = w_act_psa-l_itmguid.
CATCH cx_sy_dynamic_osql_error.
MESSAGE `Error in update!` TYPE 'I'.
WRITE:/'Error in update!'.
COMMIT WORK.
ENDTRY.
ENDLOOP.

WRITE:/'Update finished successfully.'.

[Former Member]

Chandrasekhar,

Thanks for your reply and I am sure your solution may optimize the performance. However the main problem is about th esecond loop and I am still trying to find another way for that part.

LOOP AT w_act_psa.

IF w_act_psa-l_ch_date = '00000000'.

w_act_psa-l_ch_date = w_act_psa-l_cr_date.

ENDIF.

• update active table.

TRY.

UPDATE /bic/azsc_d00300

SET /bic/zsc_date = w_act_psa-l_ch_date

WHERE /bic/zsc_acgui = w_act_psa-l_guid

AND /bic/zsc_itmgu = w_act_psa-l_itmguid.

CATCH cx_sy_dynamic_osql_error.

MESSAGE `Error in update!` TYPE 'I'.

WRITE:/'Error in update!'.

COMMIT WORK.

ENDTRY.

ENDLOOP.

ENDLOOP.

WRITE:/'Update finished successfully.'.

[Former Member]

Hi,

U can rewrite ur code in this way,

<b>--avoid select..endselect.</b>

SELECT rnr

FROM rsseldone

INTO table w_psarqst

WHERE seldate in p_date

AND source EQ p_source.

<b>--rather than looping use for all entries like:</b>

<b>--declare the field in internal table w_act_psa in the order they are selected from (l_psa)

--rather tahn specifying table dynamically like (l_psa) directly specify the table name there.</b>

if not w_psarqst is initial.

SELECT guid item_guid created_at item_changed_at

FROM (l_psa)

INTO table w_act_psa

for all entries in w_psarqst

where request = w_psarqst-low.

endif.

[hermanoclaro]

Hi there,

Maybe the problems you are getting with performance would com from this select inside the loop statement. Try to make an internal table with the fields you are using and do a for all entries to get data.

Other problem would be the nested loops. You could do a loop in the tables and do read tables or binary loops to get a better performance.

Help this helps you out.

If any answer helps you in some way reward it's user.