Java Programming with Oracle JDBC

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