Performance issue and functional question regarding updates on tables

[bruce_hartley]

A person at my site wrote some code to update a custom field on the MARC table that was being copied from the MARA table. Here is what I would have expected to see as the code. Assume that both sets of code have a parameter called p_werks which is the plant in question.

data : commit_count type i.

select matnr zfield from mara into (wa_marc-matnr, wa_marc-zfield).

update marc set zfield = wa_marc-zfield

where werks = p_werks and matnr = wa_matnr.

commit work and wait.

endselect.

I would have committed every 200 rows instead of every one row, but here's the actual code and my question isn't around the commits but something else. In this case an internal table was built with two elements - MATNR and WERKS - could have done that above too, but that's not my question.

DO.

" Lock the record that needs to be update with material creation date

CALL FUNCTION 'ENQUEUE_EMMARCS'

EXPORTING

mode_marc = 'S'

mandt = sy-mandt

matnr = wa_marc-matnr

werks = wa_marc-werks

EXCEPTIONS

foreign_lock = 1

system_failure = 2

OTHERS = 3.

IF sy-subrc <> 0.

" Wait, if the records not able to perform as lock

CALL FUNCTION 'RZL_SLEEP'.

ELSE.

EXIT.

ENDIF.

ENDDO.

" Update the record in the table MARC with material creation date

UPDATE marc SET zzdate = wa_mara-zzdate

WHERE matnr = wa_mara-matnr AND

werks = wa_marc-werks. " IN s_werks.

IF sy-subrc EQ 0.

" Save record in the database table MARC

CALL FUNCTION 'BAPI_TRANSACTION_COMMIT'

EXPORTING

wait = 'X'

IMPORTING

return = wa_return.

wa_log-matnr = wa_marc-matnr.

wa_log-werks = wa_marc-werks.

wa_log-type = 'S'.

" text-010 - 'Material creation date has updated'.

wa_log-message = text-010.

wa_log-zzdate = wa_mara-zzdate.

APPEND wa_log TO tb_log.

CLEAR: wa_return,wa_log.

ELSE.

" Roll back the record(un save), if there is any issue occurs

CALL FUNCTION 'BAPI_TRANSACTION_ROLLBACK'

IMPORTING

return = wa_return.

wa_log-matnr = wa_marc-matnr.

wa_log-werks = wa_marc-werks.

wa_log-type = 'E'.

" 'Material creation date does not updated'.

wa_log-message = text-011.

wa_log-zzdate = wa_mara-zzdate..

APPEND wa_log TO tb_log.

CLEAR: wa_return, wa_log.

ENDIF.

" Unlock the record from data base

CALL FUNCTION 'DEQUEUE_EMMARCS'

EXPORTING

mode_marc = 'S'

mandt = sy-mandt

matnr = wa_marc-matnr

werks = wa_marc-werks.

ENDIF.

Here's the question - why did this person enqueue and dequeue explicit locks like this ? They claimed it was to prevent issues - what issues ??? Is there something special about updating tables that we don't know about ? We've actually seen it where the system runs out of these ENQUEUE locks.

Before you all go off the deep end and ask why not just do the update, keep in mind that you don't want to update a million + rows and then do a commit either - that locks up the entire table!

[bruce_hartley]

A person at my site wrote some code to update a custom field on the MARC table that was being copied from the MARA table. Here is what I would have expected to see as the code. Assume that both sets of code have a parameter called p_werks which is the plant in question.

data : commit_count type i.

select matnr zfield from mara into (wa_marc-matnr, wa_marc-zfield).

update marc set zfield = wa_marc-zfield

where werks = p_werks and matnr = wa_matnr.

commit work and wait.

endselect.

I would have committed every 200 rows instead of every one row, but here's the actual code and my question isn't around the commits but something else. In this case an internal table was built with two elements - MATNR and WERKS - could have done that above too, but that's not my question.

DO.

" Lock the record that needs to be update with material creation date

CALL FUNCTION 'ENQUEUE_EMMARCS'

EXPORTING

mode_marc = 'S'

mandt = sy-mandt

matnr = wa_marc-matnr

werks = wa_marc-werks

EXCEPTIONS

foreign_lock = 1

system_failure = 2

OTHERS = 3.

IF sy-subrc <> 0.

" Wait, if the records not able to perform as lock

CALL FUNCTION 'RZL_SLEEP'.

ELSE.

EXIT.

ENDIF.

ENDDO.

" Update the record in the table MARC with material creation date

UPDATE marc SET zzdate = wa_mara-zzdate

WHERE matnr = wa_mara-matnr AND

werks = wa_marc-werks. " IN s_werks.

IF sy-subrc EQ 0.

" Save record in the database table MARC

CALL FUNCTION 'BAPI_TRANSACTION_COMMIT'

EXPORTING

wait = 'X'

IMPORTING

return = wa_return.

wa_log-matnr = wa_marc-matnr.

wa_log-werks = wa_marc-werks.

wa_log-type = 'S'.

" text-010 - 'Material creation date has updated'.

wa_log-message = text-010.

wa_log-zzdate = wa_mara-zzdate.

APPEND wa_log TO tb_log.

CLEAR: wa_return,wa_log.

ELSE.

" Roll back the record(un save), if there is any issue occurs

CALL FUNCTION 'BAPI_TRANSACTION_ROLLBACK'

IMPORTING

return = wa_return.

wa_log-matnr = wa_marc-matnr.

wa_log-werks = wa_marc-werks.

wa_log-type = 'E'.

" 'Material creation date does not updated'.

wa_log-message = text-011.

wa_log-zzdate = wa_mara-zzdate..

APPEND wa_log TO tb_log.

CLEAR: wa_return, wa_log.

ENDIF.

" Unlock the record from data base

CALL FUNCTION 'DEQUEUE_EMMARCS'

EXPORTING

mode_marc = 'S'

mandt = sy-mandt

matnr = wa_marc-matnr

werks = wa_marc-werks.

ENDIF.

Here's the question - why did this person enqueue and dequeue explicit locks like this ? They claimed it was to prevent issues - what issues ??? Is there something special about updating tables that we don't know about ? We've actually seen it where the system runs out of these ENQUEUE locks.

Before you all go off the deep end and ask why not just do the update, keep in mind that you don't want to update a million + rows and then do a commit either - that locks up the entire table!

[RaymondGiuseppi]

The ENQUEUE lock insure that another program called by another user will not update the data at the same time, so preventing database coherence to be lost. In fact, another user on a SAP correct transaction, has read the record and locked it, so when it will be updated your modifications will be lost, also you could override modifications made by another user in another luw.

You cannot use a COMMIT WORK in a SELECT - ENDSELECT, because COMMIT WORK will close each and every opened database cursor, so your first idea would dump after the first update. (so the internal table is mandatory)

Go through some documentation like [Updates in the R/3 System (BC-CST-UP)|http://help.sap.com/printdocu/core/Print46c/en/data/pdf/BCCSTUP/BCCSTUP_PT.pdf]

Regards

[Former Member]

Hi,

Why are you using the direct DB update. Use BAPI or BDC so you may not be required to worry of all ENQUE,DEQUE stuff. SAP will take care of these.

Also Direct DB update of standard table shoul be the last solution when you don't have any other option.

Regards,

Atish

[RaymondGiuseppi]

I presume (hope) that you are not in productive stage, but preparing the start of production ?

Else Atish is right, use the BAPI_MATERIAL_SAVEDATA passing your customer field in the EXTENSION parameter of the BAPI.

You may look at OSS [Note 211815 - Not all flds onl.maint.in StandardMaterial.SaveData|https://service.sap.com/sap/support/notes/211815] to add your customer fields to this BAPI interface.

Regards

[bruce_hartley]

Thanks for the link on the updates - I'm going to read that and then get back to this and possibly close this out.

Edited by: Bruce Hartley on Mar 27, 2008 7:40 PM

[bruce_hartley]

I should have mentioned what version we are on - we are running ECC 6.0 - I found some updated documentation as follows in case anyone following this is on a more recent version.

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/c1be1003-0701-0010-3795-f87... - Using ABAP - one thing though - the "update" process is nowhere nearly defined as in the prior document.