【文章內(nèi)容簡(jiǎn)介】 redStatement 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, 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。ll see that this time, the batched PreparedStatement object bees more efficient than the Statement object at about 50 inserts. This is an improvement over the prepared statement without batching.Figure 193WARNING: There39。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 proprietary batching.Now, let39。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。ll see that with the Thin driver, the use of a batched PreparedStatement object bees more efficient than a Statement object more quickly than with the OCI driverat about 40 inserts.Figure 194If you intend to perform many iterations of the same SQL statement against a database, you should consider batching with a PreparedStatement object. We39。ve finished looking at improving the performance of inserts, updates, and deletes. Now let39。s see what we can do to squeak out a little performance while selecting data.Predefined SELECT StatementsEvery time you execute a SELECT statement, the JDBC driver makes two round trips to the database. On the first round trip, it retrieves the metadata for the columns you are selecting. On the second round trip, it retrieves the actual data you selected. With this in mind, you can improve the performance of a SELECT statement by 50% if you predefine the Selecstatement by using Oracle39。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。ll see a differentiation in the timings of about 50%. Given a situation in which you need to make several tight calls to the database using a Statement, a predefined SELECT statement can save you a significant amount of time.Table 197: Select timings (in milliseconds)DriverStatementdefineColumnType( )OCI1310Thin1310Now that we39。ve looked at automit, SQL92 parsing, prepared statements, and a predefined SELECT, let39。s take a look at the performance of callable statements.CallableStatementsAs you may recall, CallableStatement objects are used to execute database stored procedures. I39。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 monly believed that calling stored procedures is faster than using SQL, but that39。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 statements into a stored procedure that you then call only once, you39。ll get better performance than if you executed each SQL statement separately from your program. This performance gain is the result of your program not having to move all the related data back and forth over the network, which is often the slowest part of the data manipulation process. This is how stored procedures are supposed to be used with Oraclenot as a substitute for SQL, but as a means to perform work where it can be done most efficiently.OCI Versus Thin DriversOracle39。s documentation states