Java Programming with Oracle JDBC

Predefined SELECT Statements

Every 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 SELECT statement by using Oracle’s defineColumnType( ) method with an OracleStatement object (see Section 9.4.2 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 19-7 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, you’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 19-7. Select timings (in milliseconds)

Driver	Statement	defineColumnType( ...