COMMIT Work

[Former Member]

Hello All,

I have a simple code:

<i><CODE>

data: begin of itab occurs0,

f1 like table-f1,

f2 like table-f2,

f3 like table-f3,

f4 like table-f4,

end of itab.

SELECT F1 F2 F3 F4 FROM TABLE INTO itab

where clause.

COMMIT WORK. <------

</CODE></i>

Is this code acceptable from a Performance Improvement point of view?

I did read the documentation of COMMIT which informs that COMMIT after the SELECT statement commits an implicit commit on the DB and the WorkProcessor attached with that particular SELECT statement gets freed. In a way this help in the Improvement.

Could any one confirm on this with any of the documents from SAP.

Thank you,

-PSK

[Former Member]

I feel you dont need a specific commit if you are just selecting the records from a table. It is surely required if you are updating/inserting records in the table.

Regards

Anurag

[TuncayKaraca]

Sravan,

If you just SELECT data from the table, you don't need to use COMMIT. Use COMMIT when you UPDATE/MODIFIY or INSERT data.

Look at this link:

<a href="http://help.sap.com/saphelp_nw04/helpdata/en/fc/eb3b64358411d1829f0000e829fbfe/frameset.htm">Committing Database Changes</a>

[Former Member]

Hi

You should have improvemement for updating/inserting, I believe to place a commit after a select you can obtain the effect to reset the time, in this way you could avoid overtime dump.

Max

[Former Member]

Hi Max,

Thanks for your posting.

Could you please enumerate your statement.

Thank you.

-PSK

[Former Member]

Hi

A program can work online only for a certain time, if this time exceed the limit, a dump for overtime occurs.

One of effect of COMMIT WORK is to reset the time of working: if it takes many time to do a select, after finishing this select an overtime dump could occur, you can try to avoid that by calling a COMMIT WORK.

Max

[Former Member]

Hi Max,

Thank you.

Is there any document or link from help.sap.com that would confirm on this. This would be of great help to me.

Thank you,

-PSK

[Former Member]

Hi

From SAP HELP:

Notes

All subroutines called with PERFORM ... ON COMMIT are processed in the LUW concluded by the COMMIT WORK command. All V1 update requests specified in CALL FUNCTION ... IN UPDATE TASK are also executed in one LUW. When all V1 update requests have been successfully concluded, the V2 update requests ("update with start delayed") are processed, each in one LUW. Parallel to this, the function modules specified in CALL FUNCTION ... IN BACKGROUND TASK are each executed in one LUW per destination.

COMMIT WORK commands processed within CALL DIALOG processing

- execute a database commit (see above),

- close all open database cursors,

<b>- reset the time slice counter and</b>

.................................

Max

[Former Member]

But don't confuse this with making the program perform better. It's just letting the program run on. Your basis people may object because you'll be tying up a dialogue process for something that should run in the background.

Rob

[Former Member]

From a performance standpoint, the commit is a trip to the database that doesn't do anything. Therefore, it will slightly degrade performance.

Rob

[Former Member]

1.) In long running programs with many different independent DB changes a commit work is very important, since due to the commit lock waits on the DB are prevented.

2.) The runtime in dialog processes is limited. As far as I know, this is independent from explicit commits.

3.) You can overcome this time limit by calling FM 'TH_REDISPATCH', but be careful!!!!

Best regards,

Thomas

[Former Member]

As a conclusion : a COMMIT will not increase the performance but can avoid a TimeOut exception.

[Former Member]

Commit after selects which select big data volume will help as even select sets locks on DB level (shared locks) and commit releases these locks.

[Former Member]

Why not avoid the issue by

Select F1 F2 F3 F4 from table

into table itab

where clause.

And make the one call to the database.

[Former Member]

Hello,

In this context COMMIT WORK don't serve the purpose of improving the performance.

It is acceptable only for the context what Max mentioned.

Regs,

Venkat Ramanan N

[Former Member]

Hi,

Yes as per the documentation it is true that it improves the performance. However, not for all the underlying database systems. It definetly improves the performance for underlying database systems like DB2. COMMIT WORK statement after Select statement does not require for database systems like ORACLE. Hence usage of COMMIT WORK depends on the underlying database systems.

thanks,

sksingh

[christian_wohlfahrt]

Hi,

I can confirm Srinivas statement: for DB2 database a commit work in read programs makes sense. DB2 creates implicit read locks. These read locks prevent other sessions from an update access.

When a lot of selects where done for one table, these locks aren't stored per entry any longer, but per pages. With more and more page locks it's getting more and more likely, that other updates have to wait. In case of really long runs (more than some hours), we had sometimes to stop the report, because the whole system was near of a complete stop:

one report with a whole bunch of read-locks, several sessions trying to update a table in a wait-status -> soon number of free sessions was going rapidly down. Especially in case of stock tables or accounting tables the updates were critical for a working system.

A commit work (on DB2 database) will release the read-locks again -> all the reports of this customer had commit works to avoid the locking.

Regards,

Christian

Do not mix this up with SAP's lock concept - that's explicit and is completely independent of the actions on DB2 level.