Q and A

Asked and Answered

Top Ten Performance Tips

Is there a rule of thumb for determining the optimal number of actions (for example, inserts) to perform before doing a commit? My applications tend to use 1000, but I don't know if there is any reason for this particular number or if it was arbitrarily chosen.

Scott Hayes responds:

How frequently your applications commit is largely a function of concurrency requirements and restart/recovery time in the event of a failure. If a commit takes 1/4 second, committing after each of 1,000,000 transactions gets expensive. However, you might do so if the application is running concurrent with transaction processing. Committing every 1,000 transactions greatly reduces commit cost, but concurrency could be impaired. In the other extreme, if you commit every 1,000,000 transactions, rollback and/or restart recovery time could be obnoxiously long if there was a hardware failure or other crash. Youýre correct that 1,000 is a commonly used number. Committing after 1,000 transactions achieves a good balance between concurrency, elapsed time, and rollback/recovery requirements. I'd probably only change from 1,000 to a lower number (10 or 50 or so) if running an application concurrently with transaction processing and lock timeouts were occurring.

See a complete archive of reader/author Q&As .

Back to Top 10 Performance Tips by Scott Hayes.