【正文】 racle 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 escape syntax allows you to make your code more portablebut does this portability e with a cost in terms of performance?Table 192 shows the number of milliseconds needed to insert 1,000 rows into the TESTXXXPERF table. Timings are shown with the SQL92 escape syntax parser on and off for both the OCI and Thin drivers. As before, these timings represent the result of three program runs averaged together. Table 192: SQL92 token parser timings (in milliseconds)SQL92 parserOCIThinOn2,5672,514Off2,7442,550Notice from Table 192 that with the OCI driver we lose 177 milliseconds when escape syntax parsing is turned off, and we lose only 37 milliseconds when the parser is turned off with the Thin driver. These results are the opposite of what you might intuitively expect. It appears that both drivers have been optimized for SQL92 parsing, so you should leave it on for best performance. Now that you know you never have to worry about turning the SQL92 parser off, let39。s move on to something that has some potential for providing a substantial performance improvement.Statement Versus PreparedStatementThere39。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。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。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 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 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 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。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。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。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 Prepa