ONJava.com -- The Independent Source for Enterprise Java
oreilly.comSafari Books Online.Conferences.

advertisement

AddThis Social Bookmark Button

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.

Chart.
Figure 19-1. OCI driver timings.

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



Chart.
Figure 19-2. Thin driver timings

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.

Chart.
Figure 19-3. OCI driver timings for batched SQL

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.

Chart.
Figure 19-4. Thin driver timings for batched SQL

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.

Pages: 1, 2, 3

Next Pagearrow