Databases Reference
In-Depth Information
network and that every inserted row was written to disk immediately following
the execution of the
Insert
. When auto-commit mode was turned off in the
application, the number of statements issued by the driver and executed on the
database server was reduced from ten million (five million
Inserts
+ five mil-
lion
Commits
) to five million and one (five million
Inserts
+ one
Commit
). As a
consequence, application processing was reduced from eight hours to ten min-
utes. Why such a dramatic difference in time? There was significantly less disk
I/O required by the database server, and there were 50% fewer network round
trips.
Performance Tip
Although turning off auto-commit mode can help application perfor-
mance, do not take this tip too far. Leaving transactions active can
reduce throughput by holding locks on rows for longer than necessary,
preventing other users from accessing the rows. Typically, committing
transactions in intervals provides the best performance as well as accept-
able concurrency.
If you have turned off auto-commit mode and are using manual commits,
when does it make sense to commit work? It depends on the following factors:
•
The type of transactions your application performs. For example, does your
application perform transactions that modify or read data? If your applica-
tion modifies data, does it update large amounts of data?
•
How often your application performs transactions.
For most applications, it's best to commit a transaction after every logical
unit of work. For example, consider a banking application that allows users to
transfer money from one account to another. To protect the data integrity of that
work, it makes sense to commit the transaction after both accounts are updated
with the new amounts.
However, what if an application allows users to generate reports of account
balances for each day over a period of months? The unit of work is a series of
Select
statements, one executed after the other to return a column of balances.
In most cases, for every
Select
statement executed against the database, a lock is
placed on rows to prevent another user from updating that data. By holding
