Java Programming with Oracle JDBC: Performance
Pages: 1, 2, 3
The OCI Driver
Table 19-3 shows the timings in milliseconds for 1
insert and 1,000 inserts in the TESTXXXPERF table. The inserts are done
first using a Statement object and then a
PreparedStatement object. If you look at the results for 1,000
inserts, you may think that a prepared statement performs better. After
all, at 1,000 inserts, the PreparedStatement object is almost
twice as fast as the Statement object, but if you examine Figure 19-1, you'll see a different story.
| Table 19-3: OCI driver timings (in milliseconds) | ||
|
Inserts |
Statement |
PreparedStatement |
|
1 |
10 |
113 |
|
1,000 |
2,804 |
1,412 |
Figure 19-1 is a graph of the timings needed to
insert varying numbers of rows using both a Statement object
and a PreparedStatement object. The number of inserts begins
at 1 and climbs in intervals of 10 up to a maximum of 150 inserts. For this
graph and for those that follow, the lines themselves are polynomial trend
lines with a factor of 2. I chose polynomial lines instead of straight
trend lines so you can better see a change in the performance as the number
of inserts increases. I chose a factor of 2 so the lines have only one
curve in them. The important thing to notice about the graph is that it's
not until about 65 inserts that the PreparedStatement object
outperforms the Statement object. 65 inserts! Clearly, the
Statement object is more efficient under typical use when
using the OCI driver.
|
The Thin Driver
If you examine Table 19-4 (which shows the same
timings as for Table 19-3, but for the Thin driver)
and Figure 19-2 (which shows the data incrementally),
you'll see that the Thin driver follows the same behavior as the OCI
driver. However, since the Statement object starts out
performing better than the PreparedStatement object, it takes
about 125 inserts for the PreparedStatement to outperform
Statement.
| Table 19-4: Thin driver timings (in milliseconds) | ||
|
Inserts |
Statement |
PreparedStatement |
|
1 |
10 |
113 |
|
1,000 |
2,583 |
1,739 |
|
When you consider typical SQL statement usage, even with the Thin
driver, you'll get better performance if you execute your SQL statements
using a Statement object instead of a
PreparedStatement object. Given that, you may ask: why use a
PreparedStatement at all? It turns out that there are some
reasons why you might use a PreparedStatement object to
execute SQL statements. First, there are several types of operations that
you simply can't perform without a PreparedStatement object.
For example, you must use a PreparedStatement object if you
want to use large objects like BLOBs or CLOBs or if you wish to use object
SQL. Essentially, you trade some loss of performance for the added
functionality of using these object technologies. A second reason to use a
PreparedStatement is its support for batching.
Batching
As you saw in the previous section, PreparedStatement
objects eventually become more efficient than their Statement
counterparts after 65-125 executions of the same statement. If you're going
to execute a given SQL statement a large number of times, it makes sense
from a performance standpoint to use a PreparedStatement
object. But if you're really going to do that many executions of a
statement, or perhaps more than 50, you should consider batching. Batching
is more efficient because it sends multiple SQL statements to the server at
one time. Although JDBC defines batching capability for
Statement objects, Oracle supports batching only when
Prepared-Statement objects are used. This makes
some sense. A SQL statement in a PreparedStatement object is
parsed once and can be reused many times. This naturally lends itself to
batching.
The OCI Driver
Table 19-5 lists Statement and batched
PreparedStatement timings, in milliseconds, for 1 insert and
for 1,000 inserts. At the low end, one insert, you take a small performance
hit for supporting batching. At the high end, 1,000 inserts, you've gained
75% throughput.
| Table 19-5: OCI driver timings (in milliseconds) | ||
|
Inserts |
Statement |
Batched |
|
1 |
10 |
117 |
|
1,000 |
2,804 |
691 |
If you examine Figure 19-3, a trend line analysis
of the Statement object versus the batched
PreparedStatement object, you'll see that this time, the
batched Prepared-Statement object becomes more
efficient than the Statement object at about 50 inserts. This
is an improvement over the prepared statement without batching.
|
WARNING: There's a catch here. The 8.1.6 OCI driver has a defect by which it does not support standard Java batching, so the numbers reported here were derived using Oracle's proprietary batching.
Now, let's take a look at batching in conjunction with the Thin driver.
The Thin Driver
The Thin driver is even more efficient than the OCI driver when it comes
to using batched prepared statements. Table 19-6 shows
the timings for the Thin driver using a Statement object
versus a batched PreparedStatement object in milliseconds for
the specified number of inserts.
| Table 19-6: Thin driver timings (in milliseconds) | ||
|
Inserts |
Statement |
Batched |
|
1 |
10 |
117 |
|
1,000 |
2,583 |
367 |
The Thin driver takes the same performance hit on the low end, one
insert, but gains a whopping 86% improvement on the high end. Yes, 1,000
inserts in less than a second! If you examine Figure
19-4, you'll see that with the Thin driver, the use of a batched
PreparedStatement object becomes more efficient than a
Statement object more quickly than with the OCI driver--at
about 40 inserts.
|
If you intend to perform many iterations of the same SQL statement
against a database, you should consider batching with a
PreparedStatement object.
We've finished looking at improving the performance of inserts, updates, and deletes. Now let's see what we can do to squeak out a little performance while selecting data.
