Maximum number of rows before doing a commit

[Former Member]

When updating a database table, is there a maximum number of rows you should update before doing a commit work?

[Former Member]

That would probably vary from installation to installation. But if you are updating a very large number of records, it would be wise to commit regularly; otherwise, you run the risk of dumping due to exceeding the size of the rollback area.

But you don't want to commit too often. It will slow down the program.

Rob

[Former Member]

Thanks, but can you be more specific? What is considered regularly verses too often?

[Former Member]

It's hard to give a specific answer. There are generally more than one rollback area in an installation and the size for the one used by batch processes may change after a certain time of day when more batch updates are being run. It will also depend on the size of the record being updated. It may also depend on whether you are in the development systemm QA or production.

But I guess, I would start with a number like 5,000 or 10,000 and see what happens.

You might also see if you can do the entire update in the QA system and commit afer it's done. If you can do it there, you'll likely be OK in production. Or if it works there, you can commit a couple of extra times in production - just to be sure.

Bear in mind though, that if you commit and the job fails later, you have to either be able to back out the updates, or start updating at the point where the previous one failed.

Rob

[Former Member]

Linda,

To follow the concept of a Logical Unit of Work (LUW), I would suggest that you not worry about a limit. Allow the database engine to handle that.

IF you do start max-ing out the rollback area, you should then review the amount of data being processed with the business and your DBAs.

If you follow the LUW concept, you will ALWAYS get ALL data saved or NO data saved. If a short dump occurs for a reason other than max-ing out the rollback, NONE of the data will be saved to the database.

At this point, you do not have a partially processed set of data to sort out.

[Former Member]

Rob,

You could be correct... hard to know.

I just stress the importance of the LUW. It is in place to protect us from ourselves.

[Former Member]

Well, I,ve been in this situation before. Letting the entire update run resulted in a dump on the rollback area. So we had to commit regularly, but then we had to consider the strategy if it failed after the first commit. It's reasonably easy if you're doing a bunch of inserts, but changes...

Rob

[Former Member]

Hello Linda,

There is no specified limit as such. It will depend upon your activity. Its always better to perform commit work after you create any business object like businees partner, installation etc..But thats taken care by the standard programs..

Whereas updating a single databsase is concerned, its better to use commit after regular intervals subject to your data and installation and ur need.

Else as John says, Use it when you need it!!

Regards,

Tanveer.

Please mark helpful answers.

Message was edited by: Tanveer Shaikh

[Former Member]

Linda,

In order to prevent getting a partially processed set of data, it is best to follow this simple rule:

Do not address a problem unless you have a problem.

If you write code to perform intermittent COMMITs, and you have a data-related issue that causes the program to dump... you have a real nightmare on your hands.

The LUW concept exists to prevent these nightmares. It is simply "all or nothing" when the data is processed.

If you code for COMMITs (when you really have NO known problem), you have just created the potential for a problem. If that program dumps, you will be SE16-ing 'til the cows come home trying to determine what did and did not get processed/updated.

[Former Member]

But remember John, the question is what's the "maximum number of rows before doing a commit". So I think the implication is that the rollback area is the issue being addressed here.

But I agree, You don't want to commit along the way if you don't have to.

Rob

[Former Member]

Hey Guys,

I believe this entirely depends upon the naturee of your application and the data you are handling..

John is right to say "USE IT , only when needed", but also we need to consider roll back issues..

Hence, i say, there is no fix answer for this question. It depends on ur object being developed..

Regards,

Tanveer.

Mark if found helpful