Auto-Commit
By default, JDBC’s auto-commit feature is on, which means that each SQL statement is committed as it is executed. If more than one SQL statement is executed by your program, then a small performance increase can be achieved by turning off auto-commit.
Let’s take a look at some numbers. Table 19-1
shows the average time, in milliseconds, needed to insert 1,000 rows
into the TESTXXXPERF table using a Statement
object. The timings represent the average from three runs of the
program. Both drivers experience approximately a one-second loss as
overhead for committing between each SQL statement. When you divide
that one second by 1,000 inserts, you can see that turning off
auto-commit saves approximately 0.001 seconds (1 millisecond) per SQL
statement. While that’s not interesting enough to write home
about, it does demonstrate how auto-commit can impact performance.
Table 19-1. Auto-commit timings (in milliseconds)
Auto-commit |
OCI |
Thin |
---|---|---|
On |
3,712 |
3,675 |
Off |
2,613 |
2,594 |
Clearly, it’s more important to turn off auto-commit for managing multistep transactions than for gaining performance. But on a heavily loaded system where many users are committing transactions, the amount of time it takes to perform commits can become quite significant. So my recommendation is to turn off auto-commit and manage your transactions manually. The rest of the tests in this chapter are performed with auto-commit turned off.
Get Java Programming with Oracle JDBC now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.