【正文】 e TESTXXXPERF table using a Statement object. The timings represent the average from three runs of the program. Both drivers experience approximately a onesecond loss as overhead for mitting between each SQL statement. When you divide that one second by 1,000 inserts, you can see that turning off automit saves approximately seconds (1 millisecond) per SQL statement. While that39。s more important to turn off automit for managing multistep transactions than for gaining performance. But on a heavily loaded system where many users are mitting transactions, the amount of time it takes to perform mits can bee quite significant. So my remendation is to turn off automit and manage your transactions manually. The rest of the tests in this chapter are performed with automit turned off. SQL92 Token ParsingLike automit, SQL92 escape syntax token parsing is on by default. In case you don39。s move on to something that has some potential for providing a substantial performance improvement.Statement Versus PreparedStatementThere39。ll look at this performance issue with respect to both the OCI driver and the Thin driver.The OCI DriverTable 193 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 191, you39。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.Figure 191The Thin DriverIf you examine Table 194 (which shows the same timings as for Table 193, but for the Thin driver) and Figure 192 (which shows the data incrementally), you39。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 can39。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 you39。ve gained 75% throughput.Table 195: OCI driver timings (in milliseconds)InsertsStatementBatched1101171,0002,804691If you examine Figure 193, a trend line analysis of the Statement object versus the batched PreparedStatement object, you39。s a catch here. The OCI driver has a defect by which it does not support standard Java batching, so the numbers reported here were derived using Oracle39。s take a look at batching in conjunction with the Thin driver.The Thin DriverThe Thin driver is even more efficient than the OCI driver when it es to using batched prepared statements. Table 196 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 196: Thin driver timings (in milliseconds)InsertsStatementBatched1101171,0002,583367The 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 194, you39。ve finished looking at improving the performance of inserts, updates, and deletes. Now let39。s defineColumnType()method with an OracleStatement object (see Defining Columns in Chapter 9). When you predefine a SELECT statement, you provide the JDBC driver with the column metadata using the defineColumnType() method, obviating the need for the driver to make a round trip to the database for that information. Hence, for a singleton SELECT, you eliminate half the work when you predefine the statement.Table 197 shows the timings in milliseconds required to select a single row from the TESTXXXPERF table. Timings are shown for when the column type has been predefined and when it has not been predefined. Timings are shown for both the OCI and Thin drivers. Although the defineColumnType() method shows little improvement with either driver in my test, on a loaded network, you39。ve looked at automit, SQL92 parsing, prepared statements, and a predefined SELECT, let39。ve saved CallableStatement objects until last, because they are the slowest performers of all the JDBC SQL execution interfaces. This may sound counterintuitive, because it39。s simply not true. Given a simple SQL statement, and a stored procedure call that acplishes the same task, the simple SQL statement will always execute faster. Why? Because with the stored procedure, you not only have the time needed to execute the SQL statement but also the time needed to deal with the overhead of the procedure call itself. Table 198 lists the relative time, in milliseconds, needed to call the stored procedure TESTXXXPERF$.SETTESTXXXPERF（）. This stored procedure inserts one row into the table TESTXXXPERF. Timings are provided for both the OCI and Thin drivers. Notice that both drivers are slower when inserting a row this way than when using either a statement or a batched prepared statement (refer to Tables 193 through 196). Common sense will tell you why. The SETTESTXXXPERF（）procedure inserts a row into the database. It does exactly the same thing that the other JDBC objects did but with the added overhead of a round trip for executing the remote procedure call.Table 198: Stored procedure call timings (in milliseconds)InsertsOCIThin11131171,0001,7231,752Stored procedures do have their uses. If you have a plex task that requires several SQL statements to plete, and you encapsulate those SQL