【正文】 File。s take a look at some numbers from Windows 2000. Table 199 lists all the statistics we39。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 monly believed that calling stored procedures is faster than using SQL, but that39。s take a look at the performance of callable statements.CallableStatementsAs you may recall, CallableStatement objects are used to execute database stored procedures. I39。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。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。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。s proprietary batching.Now, let39。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。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 PreparedStatement 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 DriverTable 195 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, you39。t perform without PreparedStatement 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 ofusing these object second reason to use a PreparedStatement is its support for batching.BatchingAs you saw in the previous section, PreparedStatement objects eventually bee more efficient than their Statement counterparts after 65125 executions of the same statement. If you39。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 194: Thin driver timings (in milliseconds)InsertsStatementPreparedStatement1101131,0002,5831,739Figure 192When you consider typical SQL statement usage, even with the Thin driver, you39。ll see a different story.Table 193: OCI driver timings (in milliseconds)InsertsStatementPreparedStatement1101131,0002,8041,412Figure 191 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 it39。s a popular belief that using a PreparedStatement object is faster than using a Statement object. After all, a prepared statement has to verify its metadata against the database only once, while a statement has to do it every time. So how could it be any other way? Well, the truth of the matter is that it takes about 65 iterations of a prepared statement before its total time for execution catches up with a statement. When it es to which SQL statement object performs better under typical use, a Statement or a PreparedStatement, the truth is that the Statement object yields the best performance. When you consider how SQL statements are typically used in an application1 or 2 here, maybe 1020 (rarely more) per transactionyou realize that a Statement object will perform them in less time than a PreparedStatement object. In the next two sections, we39。t recall, SQL92 token parsing allows you to embed SQL92 escape syntax in your SQL statements (see Oracle and SQL92 Escape Syntax in Chapter 9). These standardsbased snippets of syntax are parsed by a JDBC driver transforming the SQL statement into its native syntax for the target database. SQL92 esca