Chapter 19: Performance

Performance is usually considered an issue at the end of a development
cycle when it should really be considered from the start. Often, a task
called "performance tuning" is done after the coding is complete,
and the end user of a program complains about how long it takes the program
to complete a particular task. The net result of waiting until the end of
the development cycle to consider performance includes the expense of the
additional time required to recode a program to improve its performance.
It's my opinion that performance is something that is best considered at
the start of a project.

When it comes to performance issues concerning JDBC programming there
are two major factors to consider. The first is the performance of the
database structure and the SQL statements used against it. The second is
the relative efficiency of the different ways you can use the JDBC
interfaces to manipulate a database.

In terms of the database's efficiency, you can use the EXPLAIN PLAN
facility to explain how the database's optimizer plans to execute your SQL
statements. Armed with this knowledge, you may determine that additional
indexes are needed, or that you require an alternative means of selecting
the data you desire.

On the other hand, when it comes to using JDBC, you need to know ahead
of time the relative strengths and weaknesses of using auto-commit, SQL92
syntax, and a Statement versus a
PreparedStatement versus a CallableStatement
object. In this chapter, we'll examine the relative performance of various
JDBC objects using example programs that report the amount of time it takes
to accomplish a given task. We'll first look at auto-commit. Next, we'll
look at the impact of the SQL92 syntax parser. Then we'll start a series of
comparisons of the Statement object versus the
PreparedStatement object versus the
CallableStatement object. At the same time we'll also examine
the performance of the OCI versus the Thin driver in each situation to see
if, as Oracle's claims, there is a significant enough performance gain with
the OCI driver that you should use it instead of the Thin driver. For the
most part, our discussions will be based on timing data for 1,000 inserts
into the test performance table TESTXXXPERF. There are separate programs
for performing these 1,000 inserts using the OCI driver and the Thin
driver.

The performance test programs themselves are very simple and are
available online with the rest of the examples in this book. However, for
brevity, I'll not show the code for the examples in this chapter. I'll only
talk about them. Although the actual timing values change from system to
system, their relative values, or ratios from one system to another, remain
consistent. The timings used in this chapter were gathered using Windows
2000. Using objective data from these programs allows us to come to factual
conclusions on which factors improve performance, rather than relying on
hearsay.

I'm sure you'll be surprised at the reality of performance for these
objects, and I hope you'll use this knowledge to your advantage. Let's get
started with a look at the testing framework used in this chapter.

A Testing Framework

For the most part, the test programs in this chapter report the timings
for inserting data into a table. I picked an INSERT statement because it
eliminates the performance gain of the database block buffers that may skew
timings for an UPDATE, DELETE, or SELECT statement.

The test table used in the example programs in this chapter is a simple
relational table. I wanted it to have a NUMBER, a small VARCHAR2, a large
VARCHAR2, and a DATE column. Table TESTXXXPERF is defined as:

The initial extent size used for the table makes it unlikely that the
database will need to take the time to allocate another extent during the
execution of one of the test programs. Therefore, extent allocation will
not impact the timings. Given this background, you should have a context to
understand what is done in each section by each test program.

Auto-Commit

By default, JDBC's auto-commit feature is on, which means that each SQL
statement is committed as it is executed. If more than one SQL statement is
executed by your program, then a small performance increase can be achieved
by turning off auto-commit.

Let's take a look at some numbers. Table 19-1 shows
the average time, in milliseconds, needed to insert 1,000 rows into the
TESTXXXPERF table using a Statement object. The timings
represent the average from three runs of the program. Both drivers
experience approximately a one-second loss as overhead for committing
between each SQL statement. When you divide that one second by 1,000
inserts, you can see that turning off auto-commit saves approximately 0.001
seconds (1 millisecond) per SQL statement. While that's not interesting
enough to write home about, it does demonstrate how auto-commit can impact
performance.

Table 19-1: Auto-commit timings (in milliseconds)

Auto-commit

OCI

Thin

On

3,712

3,675

Off

2,613

2,594

Clearly, it's more important to turn off auto-commit for managing
multistep transactions than for gaining performance. But on a heavily
loaded system where many users are committing transactions, the amount of
time it takes to perform commits can become quite significant. So my
recommendation is to turn off auto-commit and manage your transactions
manually. The rest of the tests in this chapter are performed with
auto-commit turned off.

SQL92 Token Parsing

Like auto-commit, SQL92 escape syntax token parsing is on by default. In
case you don't recall, SQL92 token parsing allows you to embed SQL92 escape
syntax in your SQL statements (see "Oracle and SQL92 Escape
Syntax" in Chapter 9). These standards-based 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 portable--but does this portability come with a cost in terms of
performance?

Table 19-2 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 19-2: SQL92 token parser timings (in milliseconds)

SQL92 parser

OCI

Thin

On

2,567

2,514

Off

2,744

2,550

Notice from Table 19-2 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, let's move on to something that has some potential for providing a
substantial performance improvement.

Statement Versus PreparedStatement

There'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. This has performance implications for your
application, and exploring these issues is what this section is all about.

When it comes 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
application--1 or 2 here, maybe 10-20 (rarely more) per transaction--you
realize that a Statement object will perform them in less time
than a PreparedStatement object. In the next two sections,
we'll look at this performance issue with respect to both the OCI driver
and the Thin driver.