topic Re: possibility of avoiding commit in Application Development and Automation Discussions

possibility of avoiding commit

Former Member — Wed, 07 Mar 2012 12:06:37 GMT

hello experts,

scenario is that commit and wait statement has been used inside a loop.this looping takes place for thousands of times.due to this commit and save statement it takes nearly 1 hr to execute the report.inside the loop after this commit statement,many function modules are called.

if i tried to give the commit and wait statement outside loop.the result is that database is having only the first entry value on going to the function modules inside the loop.

please help.

Re: possibility of avoiding commit

former_member192432 — Wed, 07 Mar 2012 12:25:50 GMT

Hi Namitha,

Performance wise COMMIT WORK is good. So try to use only COMMIT WORK.

Let me know are you using any UPDATE TASK FM before COMMIT stmt .

Kind regards

Chetan

Re: possibility of avoiding commit

Former Member — Wed, 07 Mar 2012 12:26:52 GMT

Hi namitha,

I am not quite sure if I got your issue completely.

But, try to update the database and calling the function modules in two different loops, if it is feasible.

And you can use commit in between these loops.

P.S: If possible try to update the database at one go(may be using an internal table) instead of doing it in a loop so that this can be solved with one loop.

Regards

Ram

Re: possibility of avoiding commit

Former Member — Wed, 07 Mar 2012 12:50:24 GMT

Hi ,

If you are using BAPI .

After your commit call this BAPI BAPI_TRANSACTION_ROLLBACK

Thanks,

Pradeep.

Re: possibility of avoiding commit

Former Member — Wed, 07 Mar 2012 14:47:05 GMT

Hello Namitha,

Why dont you use ' INSERT Ztable FROM TABLE Internal table. COMMIT WORK '. This will definately increase the performance.

Thanks,

Prakash Reddy.

Re: possibility of avoiding commit

alex_campbell — Wed, 07 Mar 2012 20:56:51 GMT

Hi Namitha,

The problem is not that the COMMIT WORK is taking so long, the problem is likely that you are calling a BAPI, and BAPIs have a lot of overhead, including a number of other function module calls which occur at COMMIT WORK (google CALL FUNCTION - IN UPDATE TASK for more info on this). Depending on which BAPI you are calling, there may be a more efficient way to update the database, but generally that is not recommended. The reason why BAPIs take so long is because they do all sorts of checking and validating and cross-table updating to ensure that the updates you are making are consistant.

Also, I suggest you use BAPI_TRANSACTION_COMMIT instead of COMMIT WORK when committing changes made by a BAPI.

Good Luck!

Alex

Re: possibility of avoiding commit

Former Member — Thu, 08 Mar 2012 07:57:35 GMT

hello,

i have used 1 function module to change the date before the commit statement.

regards,

Namitha

Re: possibility of avoiding commit

yuri_ziryukin — Thu, 08 Mar 2012 08:32:41 GMT


Hi ,
> 
> If you are using BAPI .
> 
> After your commit call this BAPI BAPI_TRANSACTION_ROLLBACK
> 
> 
> Thanks,
> Pradeep.

)))))))))

Re: possibility of avoiding commit

yuri_ziryukin — Thu, 08 Mar 2012 08:35:01 GMT


Hi Namitha,
> 
> Also, I suggest you use BAPI_TRANSACTION_COMMIT instead of COMMIT WORK when committing changes made by a BAPI.
> 
> Good Luck!
> Alex

BAPI_TRANSACTION_COMMIT is almost identical to COMMIT WORK. The difference is a subsequent call to function BUFFER_REFRESH_ALL which will not really help in this case.

Re: possibility of avoiding commit

yuri_ziryukin — Thu, 08 Mar 2012 08:42:11 GMT


hello experts,
> 
> scenario is  that commit and wait statement has been used inside a loop.this looping takes place for thousands of times.due to this commit and save statement it takes nearly 1 hr to execute the report.inside the loop after this commit statement,many function modules are called.
> 
> if i tried to give the commit and wait statement outside loop.the result is that database is having only the first entry value on going to the function modules inside the loop.
> 
> please help.

Hello Namitha,

well, if your subsequent function calls assume that the document is commited, you can hardly do anything about it. But I would like to propose you something that I have already proposed in another thread.

If you process many documents in loop in the following way:

LOOP.

CREATE DOCUMENT

COMMIT

CHANGE DOCUMENT or CREATE RELATED DOCUMENT.

COMMIT

ENDLOOP

Then you can make the following change of your algorythm:

LOOP.

CREATE DOCUMENT

ENDLOOP.

COMMIT

LOOP.

CHANGE DOCUMENT or CREATE RELATED DOCUMENT.

ENDLOOP.

COMMIT

This change will allow you to minimize the number of COMMITs and significantly increase the performance.

Of course you'll have to take care of error handling and reprocessing.

Regards,

Yuri

Re: possibility of avoiding commit

Former Member — Fri, 09 Mar 2012 09:08:27 GMT

hello,

thanks for your help.

if i do this problem in more than 1 loop as u have told i.e.

loop

endloop

commit work and wait.

loop

...

endloop,then my requirement will go.

here after the completion of 1st loop only db gets updated.

but after each item i want db to be updated. the function modules after the commit statement is a requirement as per the obtaining results from the previous function modules.so can u suggest other ways

thanks,

Re: possibility of avoiding commit

yuri_ziryukin — Fri, 09 Mar 2012 10:11:18 GMT


> here after the completion of 1st loop only db gets updated.
> but after each item i want db to be updated. the function modules after the commit statement is a requirement as per the obtaining results from the previous function modules.so can u suggest other ways
> 
> thanks,

Hello Namitha,

could you please be more specific. I did not really understand your requirement.

And if you post here your coding, it will also help to better understand your use case.

Yuri

Re: possibility of avoiding commit

Former Member — Tue, 24 Apr 2012 09:31:50 GMT

hello,

This is the code and its taking time to execute when thousands of records are there in the internal table.

LOOP AT row_no INTO wa_rowno.
READ TABLE g_t_aeoi2 INTO wa_aeoi1 INDEX wa_rowno-row_id.
IF sy-subrc = 0.

CALL FUNCTION 'CALO_INIT_API'
         EXPORTING
           flag_collect_msg_on      = 'X'
           data_reset_sign          = '!'
         EXCEPTIONS
           log_object_not_found     = 1
           log_sub_object_not_found = 2
           OTHERS                   = 3.
       IF sy-subrc = 0 .

g_f_material = wa_aeoi1-usobj(18).
         g_f_plant = wa_aeoi1-usobj+18(4).
         g_f_bom_usage = wa_aeoi1-usobj+22(1).
         g_f_stlnr = wa_aeoi1-objkt+1.

IF sy-ucomm = 'OBJ_TO_ALT' AND wa_aeoi1-aedtn NE wa_aedt-aedtn.
           CALL FUNCTION 'CCAP_ASSIGN_OBJECT_TO_ALT_DATE'
             EXPORTING
               alt_date                 = wa_aedt-aedtn
               change_no                = aenr-aennr
               chg_object_type          = '1'
               bom_category             = 'M'
               bom_usage                = g_f_bom_usage
               material                 = g_f_material
               plant                    = g_f_plant
*             FL_DELETE                =
               fl_commit_and_wait       = ''   "'X'
               fl_no_commit_work        = 'X'

*              FL_NO_COMMIT_WORK        =
             EXCEPTIONS
               error                    = 1
               OTHERS                   = 2                     .
           IF sy-subrc <> 0.
             MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
                   WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
             ROLLBACK WORK.
           ENDIF.

         ELSEIF ( sy-ucomm = 'CANC' ) AND ( wa_aedt-aedtn EQ wa_aeoi1-aedtn ).
           CALL FUNCTION 'CCAP_ASSIGN_OBJECT_TO_ALT_DATE'
             EXPORTING
               alt_date           = wa_aedt-aedtn
               change_no          = aenr-aennr
               chg_object_type    = '1'
               bom_category       = 'M'
               bom_usage          = g_f_bom_usage
               material           = g_f_material
               plant              = g_f_plant
               fl_delete          = 'X'
               fl_commit_and_wait = ''    " 'X'
               fl_no_commit_work = 'X'

           EXCEPTIONS
               error              = 1
               OTHERS             = 2.
           IF sy-subrc <> 0.
             MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
                   WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
             ROLLBACK WORK.
           ENDIF.
         ENDIF.
       ENDIF.

CALL FUNCTION 'CALO_LOG_READ_MESSAGES'
         EXPORTING
           log_class               = '4'
           language                = sy-langu
         TABLES
           messages_and_parameters = lt_msg
         EXCEPTIONS
           warning                 = 1
           error                   = 2
           OTHERS                  = 3.

       IF NOT lt_msg[] IS INITIAL.
         READ TABLE lt_msg INTO wa_msg WITH KEY msg_id = 'CC' msg_no = '142'.
         IF sy-subrc = 0 .

ROLLBACK WORK.
         ELSE.
*          lt_flg = 'X'.
           COMMIT WORK AND WAIT.
         ENDIF.
         REFRESH lt_msg.
       ENDIF.

CALL METHOD o_cma->getvalidfromdateofobject
         EXPORTING
           objecttype    = '02'
           usobj         = wa_aeoi1-usobj
         IMPORTING
           validfromdate = validfrom_date.

       IF ( wa_aeoi1-aedtn EQ wa_aedt-aedtn ) AND ( sy-ucomm = 'CANC' ).
         CLEAR: wa_aeoi1-aedtn,wa_aeoi1-datuv,wa_aeoi1-aedtz.
       ELSEIF ( wa_aeoi1-aedtn NE wa_aedt-aedtn ) AND ( sy-ucomm = 'OBJ_TO_ALT' ).
         READ TABLE g_t_aedt INTO wa_aedt WITH KEY datuv = validfrom_date aedtn = wa_aedt-aedtn.
         IF sy-subrc = 0.
           wa_aeoi1-aedtn = wa_aedt-aedtn.
           wa_aeoi1-datuv = wa_aedt-datuv.
           wa_aeoi1-aedtz = wa_aedt-aedtz.
         ELSE.
           wa_aeoi1-aedtn = ''.
           wa_aeoi1-datuv = ''.
           wa_aeoi1-aedtz = ''.
         ENDIF.
       ENDIF.
       wa_aeoi1-bom_datuv = validfrom_date.

       MODIFY g_t_aeoi2 FROM wa_aeoi1 INDEX wa_rowno-row_id TRANSPORTING aedtn datuv aedtz bom_datuv.
       MODIFY g_t_aeoi1 FROM wa_aeoi1 TRANSPORTING aedtn datuv aedtz bom_datuv WHERE
                       PLANT = WA_AEOI1-PLANT and
                       OBJKT = WA_AEOI1-OBJKT and
                       USOBJ = WA_AEOI1-USOBJ.

       CALL FUNCTION 'CSDE_BOM_DATE_CHECK'
         EXPORTING
           eaennr               = wa_aeoi1-aennr
           edatuv               = wa_aedt-datuv   "ALT DATE VALID FROM
           edatuv_old           = validfrom_date "BOM VALID FROM DATE OLD
           estlnr               = g_f_stlnr
           estlty               = 'M'
*                 FLG_AEPAR            = ' '
        EXCEPTIONS
          date_error           = 1
          date_warning         = 2
          foreign_lock         = 3
          system_failure       = 4
          OTHERS               = 5 .

       CALL FUNCTION 'CALO_LOG_READ_MESSAGES'
         EXPORTING
           log_class               = '4'
           language                = sy-langu
         TABLES
           messages_and_parameters = lt_msg
         EXCEPTIONS
           warning                 = 1
           error                   = 2
           OTHERS                  = 3.

       CLEAR g_f_name.

       LOOP AT lt_msg INTO wa_msg WHERE ( msg_type = 'E' OR msg_type = 'S' OR msg_type = 'W' ) AND msg_no NE 143.
         CONCATENATE wa_msg-msg_txt ':' g_f_material '/' g_f_plant '/' g_f_bom_usage INTO wa_listtab.
*                  COLLECT wa_listtab INTO g_t_listtab .
         IF wa_msg-msg_no = 142.
           g_f_name = text-017.
           CLEAR wa_aeoi1-message.
           CONCATENATE g_f_material '/' g_f_plant '/' g_f_bom_usage INTO wa_aeoi1-message.
           REPLACE '&1' IN g_f_name WITH wa_aedt-aedtn.
           REPLACE '&2' IN g_f_name WITH wa_aeoi1-message.
*                    wa_aeoi1-message = wa_listtab.
           wa_aeoi1-message = g_f_name.
           wa_listtab = g_f_name.
           COLLECT wa_listtab INTO g_t_listtab .
           MODIFY g_t_aeoi2 FROM wa_aeoi1 INDEX wa_rowno-row_id TRANSPORTING message.
           DELETE lt_msg WHERE msg_no = '233' AND msg_id = 'CC'.
         ELSE.
           COLLECT wa_listtab INTO g_t_listtab .
         ENDIF.
       ENDLOOP.

     ENDIF.
   ENDLOOP.

could you please help here.

Regards.

Re: possibility of avoiding commit

RaymondGiuseppi — Tue, 24 Apr 2012 10:05:16 GMT

You could try to remove the AND WAIT, but insure that no lock will arise during execution of the next call of the BAPI, if the BAPI/transaction needs to lock some shared objects between two calls, you can get some locked records until the update tasks release the locks.

Else you could try to parallelize the update in independent block of data, when you are insured that no lock can arise between the processes?

Regards,

Raymond

Re: possibility of avoiding commit

yuri_ziryukin — Thu, 26 Apr 2012 12:49:51 GMT

Hello Namitha,

your coding example is a perfect sample for 2 loops approach as I suggested above.

In the 1st loop you call CCAP_ASSIGN_OBJECT_TO_ALT_DATE for all objects (or with packages) w/o COMMIT and you commit at the end.

Then you make a second loop and call CSDE_BOM_DATE_CHECK for the same list of objects.

I hope now it's clear enough.

Yuri