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 ...
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.