Need help to do mass update

[Former Member]

Hi all,

I am working on a code and having a huge performance problem. I need to find a way to do mass update instead of reading line by line. Please see the loop that causes performance problem and if you have any idea to enhance the loop to do mass update please let me know. I appreciate for any help.

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

Regards,

Mike

[Former Member]

Hi all,

I am working on a code and having a huge performance problem. I need to find a way to do mass update instead of reading line by line. Please see the loop that causes performance problem and if you have any idea to enhance the loop to do mass update please let me know. I appreciate for any help.

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

Regards,

Mike

[bettinasilveira]

Try this:

UPDATE dbtab FROM TABLE itab.

Instead of doing a loop and an update.

[Former Member]

Hi Bettina,

I need to update only one field. With your opinion it will probably delete the other fileds. Do you know how to update just one field ?Thank you very much for your time.

Here is the whole code:

TABLES:

rsseldone,

rstsods,

/bic/azsc_d00300.

*************Selection Parameters*****************

PARAMETERS: p_psa TYPE rsodsname DEFAULT '0CRM_SALES_ACT_I_BC',

p_source TYPE rsisource DEFAULT 'ZCRM_CRM_SALES_ACT_I'.

SELECT-OPTIONS: p_date FOR sy-datum.

DATA: l_i_date TYPE dats,

t_idx LIKE sy-tabix,

l_psa(72) TYPE c,

l_psareq(30) TYPE c.

*************internal tables**********************

DATA:

BEGIN OF w_act_psa OCCURS 10,

l_guid TYPE /bic/oizsc_acgui,

l_itmguid TYPE /bic/oizsc_itmgu,

l_cr_date TYPE /bic/oizsc_date,

l_ch_date TYPE /bic/oizsc_date,

END OF w_act_psa.

DATA: w_act_ods TYPE TABLE OF /bic/azsc_d00300,

w_act_ods_line LIKE LINE OF w_act_ods.

• Range for activity item psa requests***

RANGES w_psarqst FOR l_psareq.

w_psarqst-sign = 'I'.

w_psarqst-option = 'EQ'.

*

• Find tecnical name of the table for ACTIVITY item psa

SELECT odsname_tech INTO l_psa FROM rstsods

WHERE odsname LIKE p_psa

AND dateto EQ '99990101' AND objstat EQ 'ACT'.

ENDSELECT.

• Fill up the activity request range for activity psa request to improve query performance.

• Select all activity PSA requests loaded in the given period.

IF p_date IS INITIAL.

l_i_date = sy-datum.

p_date-high = sy-datum.

l_i_date = l_i_date - 90.

p_date-low = l_i_date.

ENDIF.

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

ENDTRY.

ENDLOOP.

ENDLOOP.

WRITE:/'Update finished successfully.'.

[Former Member]

For a start I would change the following: -

SELECT.....

APPEND

ENDSELECT.

to

SELECT...

INTO TABLE.

On you Update statment are you specifying the full key for the record.

How may records are you expecting to update.

[Former Member]

Hi Martin,

It is around 24 000 000. Thanks

[bettinasilveira]

Maybe you can do a select from the table /bic/azsc_d00300 with all the fields.

Move the values you want to update to the internat table and then do the update from table.

I see that you are not checking if the key you are trying to update exists in the /bic/azsc_d00300. If not exist you'll get an SY-SUBRC = 4. In this way you are trying to update just the rows you want and you have all the fields.

Just an idea!

Try to not use the select/end select and not use select/update/insert/etc in a loop.

Anyway. For that amount of records you should run that program as a batch process.

[Former Member]

Thanks Bettina,

The whole story is this actually; This ODS is too big and we are unfortunatelly not allowed to reload it. We need to see a date in the report and there is no charactesitic was created in the ODS. However, the data comes to the PSA and this is the whole prupose of this code; getting the data from PSA with primary keys and fill ne new info object in the ODS. Of course I will use batch process to run it but I still need to find an answer for <b>" How to do Mass Update for only one field"</b>.

I cannot use UPDATE dbtab FROM TABLE itab option since itab and dbtab structures doesn't match.

I would appreciate any furher help.

Regards.

Mike

[Former Member]

Some questions for you:

Is this a standard SAP table you are updating?

Does your WHERE include the primary key?

Your problem appears to be doing this in two nested loops.

Rob

[Former Member]

Rob,

I agree and thanks for your opinions, I got rid off the nested loop but still need to find a way to do UPDATE for only one filed for two diffrernt structures.

Thanks,