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 ... |
Get Java Programming with Oracle JDBC now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.