topic Re: Performance problem with an abap code in Application Development and Automation Discussions

Performance problem with an abap code

Former Member — Wed, 14 Feb 2007 15:23:06 GMT

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.

WRITE:/'Update finished successfully.'.

Re: Performance problem with an abap code

Former Member — Wed, 14 Feb 2007 15:27:27 GMT

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

Re: Performance problem with an abap code

Former Member — Wed, 14 Feb 2007 15:31:56 GMT

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

Re: Performance problem with an abap code

Former Member — Wed, 14 Feb 2007 15:34:40 GMT

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

Message was edited by:

Mike Curtis

Re: Performance problem with an abap code

amit_khare — Wed, 14 Feb 2007 15:36:31 GMT

Hi,

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

SELECT rnr FROM rsseldone INTO w_psarqst-low

WHERE seldate IN p_date

AND source EQ p_source.

APPEND w_psarqst.

ENDSELECT.

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

SELECT guid item_guid created_at item_changed_at FROM (l_psa) INTO

table wa_act_psa WHERE request in w_psarqst.

Regards,

Amit

Re: Performance problem with an abap code

Former Member — Wed, 14 Feb 2007 15:37:05 GMT

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.'.

Re: Performance problem with an abap code

Former Member — Wed, 14 Feb 2007 15:37:41 GMT

Hi,

U can rewrite ur code in this way,

--avoid select..endselect.

SELECT rnr

FROM rsseldone

INTO table w_psarqst

WHERE seldate in p_date

AND source EQ p_source.

--rather than looping use for all entries like:

--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.

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.

Re: Performance problem with an abap code

Former Member — Wed, 14 Feb 2007 15:57:44 GMT

Sudheer,

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

Thanks

Re: Performance problem with an abap code

Former Member — Wed, 14 Feb 2007 16:21:33 GMT

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.

WRITE:/'Update finished successfully.'.

Re: Performance problem with an abap code

hermanoclaro — Wed, 14 Feb 2007 16:54:11 GMT

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.