Java Programming with Oracle JDBC: Performance
Pages: 1, 2, 3

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 "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 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( )
OCI	13	10
Thin	13	10

Now that we've looked at auto-commit, SQL92 parsing, prepared statements, and a predefined SELECT, let's take a look at the performance of callable statements.

CallableStatements

As you may recall, CallableStatement objects are used to execute database stored procedures. I've saved CallableStatement objects until last, because they are the slowest performers of all the JDBC SQL execution interfaces. This may sound counterintuitive, because it's commonly believed that calling stored procedures is faster than using SQL, but that's simply not true. Given a simple SQL statement, and a stored procedure call that accomplishes 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 19-8 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 19-3 through 19-6). 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 19-8: Stored procedure call timings (in milliseconds)
Inserts	OCI	Thin
1	113	117
1,000	1,723	1,752

Stored procedures do have their uses. If you have a complex task that requires several SQL statements to complete, and you encapsulate those SQL statements into a stored procedure that you then call only once, you'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 Oracle--not as a substitute for SQL, but as a means to perform work where it can be done most efficiently.

OCI Versus Thin Drivers

Oracle's documentation states that you should use the OCI driver for maximum performance and the Thin driver for maximum portability. However, I recommend using the Thin driver all the time. Let's take a look at some numbers from Windows 2000. Table 19-9 lists all the statistics we've covered in this chapter.

Table 19-9: OCI versus Thin driver timings (in milliseconds)
Metric	OCI	Thin
1,000 inserts with auto-commit	3,712	3,675
1,000 inserts with manual commit	2,613	2,594
1 insert with Statement	10	10
1,000 inserts with Statement	2,804	2,583
1 insert with PreparedStatement	113	113
1,000 inserts batched	1,482	367
SELECT	10	10
Predefined SELECT	10	10
1 insert with CallableStatement	113	117
1,000 inserts with CallableStatement	1,723	1,752
Totals	12,590	11,231

As you can see from Table 19-9, the Thin driver clearly outperforms the OCI driver for every type of operation except executions of CallableStatement objects. On a Unix platform, my experience has been that the CallableStatement numbers are tilted even more in favor of the OCI driver. Nonetheless, you can feel completely comfortable using the Thin driver in almost any setting. The Thin driver has been well-tuned by Oracle's JDBC development team to perform better than its OCI counterpart.

Donald Bales is a Systems Architect, Computer Applications Consultant, and Business Analyst specializing in the analysis, design, and programming of web-based, client-server, and distributed applications, the internationalization of existing applications, systems integration, and data warehousing.

---

View catalog information for Java Programming with Oracle JDBC.

Return to ONJava.com.