Java Programming with Oracle JDBC

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 Table 19-3 through Table 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 ...