Prepared statement pools operate on a per-connection basis. If one thread in a program pulls a JDBC connection out of the pool and uses a prepared statement on that connection, the information associated with the statement will be valid only for that connection. A second thread that uses a second connection will end up establishing a second pooled instance of the prepared statement. In the end, each connection object will have its own pool of all the prepared statements used in the application (assuming that they are all used over the lifetime of the application).

This is one reason why a standalone JDBC application should use a connection pool (JPA transparently creates a connection pool for Java SE programs, or uses a connection pool from the application server when used in a Java EE environment). It also means that the size of the connection pool matters (to both JDBC and JPA programs). That is particularly true early in the program’s execution: when a connection that has not yet used a particular prepared statement is used, that first request will be a little slower.

The size of the connection pool also matters because it is caching those prepared statements, which take up of heap space (and often a lot of heap space). Object reuse is certainly a good thing in this case, but you must be aware of how much space those reusable objects take up and make sure it isn’t negatively affecting the garbage collection time.

The second thing to consider about the prepared statement pool is what piece of code will actually create and manage the pool. Prepared statement pooling was introduced in JDBC 3.0, which provides a single method (the setMaxStatements() method of the ConnectionPoolDataSource class) to enable or disable statement pooling.^[71] That interface specifically does not define where the statement pooling should occur—whether in the JDBC driver, or some other layer such as the application server. And that single interface is insufficient for some JDBC drivers, which require additional configuration.

So, when writing a Java SE application that uses JDBC calls directly, there are two choices: either the JDBC driver must be configured to create and manage the statement pool, or the pool must be created and managed within the application code. Java EE applications have two (slightly different) possibilities: the JDBC driver can create and manage the pool, or the application server can create and manage the pool.

The tricky thing is that there are no standards in this area. Some JDBC drivers do not provide a mechanism to pool statements at all; they expect to be used only within an application server that is doing the statement pooling and want to provide a simpler driver. Some application servers do not provide and manage a pool; they expect the JDBC driver to handle that task and don’t want to complicate their code. Both arguments have merit (though a JDBC driver that does not provide a statement pool puts a burden on you if you are the developer of a standalone application). In the end, you’ll have to sift through this landscape and make sure that the statement pool is created somewhere.

Since there are no standards, you may encounter a situation where both the JDBC driver and the application server are capable of managing the prepared statement pool. In that case, it is important that only one of them be configured to do so. From a performance perspective, the better choice will again depend on the exact combination of driver and server. As a general rule, you can expect the JDBC driver to perform better statement pooling. Since the driver is (usually) specific to a particular database, it can be expected to make better optimizations for that database than the more generic application server code.

To enable statement pooling (or caching) for a particular JDBC driver, consult that driver’s documentation. In many cases, you need only set up the driver so that the maxStatements property is set to the desired value (that is, the size of the statement pool). Other drivers may require additional settings (e.g., the Oracle JDBC drivers require that specific properties be set to tell it whether to use implicit or explicit statement caching).

Transactions are present within both JDBC and JPA applications, but JPA manages transactions differently (those details are discussed later in this chapter). For JDBC, transactions begin and end based on how the Connection object is used.

In basic JDBC usage, connections have a notion of an autocommit mode (set via the setAutoCommit() method). If autocommit is turned on (and for most JDBC drivers, that is the default), then each statement in a JDBC program is its own transaction. In that case, a program need take no action to commit a transaction (in fact, if the commit() method is called, performance will often suffer).

If autocommit is turned off, then a transaction implicitly begins when the first call is made on the connection object (e.g., by calling the executeQuery() method). The transaction continues until the commit() method (or the rollback() method) is called. A new transaction will begin when the connection is used for the next database call.

Transactions are expensive to commit, so one goal is to perform as much work in a transaction as is possible. Unfortunately, that principle is completely at odds with another goal: because transactions can hold locks, they should be as short as possible. There is definitely a balance here, and striking the balance will depend on the application and its locking requirements. The next section on transaction isolation and locking covers that in more detail; first let’s look into the options for optimizing the transaction handling itself.

Consider some sample code that inserts data into a database for use by the stock application. For each day of valid data, one row must be inserted into the STOCKPRICE table, and five rows into the STOCKOPTIONPRICE table. A basic loop to accomplish that looks like this:

Connection c = DriverManager.getConnection(URL, user, pw);
PreparedStatement ps = c.prepareStatement(insertStockSQL);
PreparedStatement ps2 = c.prepareStatement(insertOptionSQL)) {
Date curDate = new Date(startDate.getTime());
while (!curDate.after(endDate)) {
    StockPrice sp = createRandomStock(symbol, curDate);
    if (sp != null) {
        ps.clearParameters();
        ps.setBigDecimal(1, sp.getClosingPrice());
        // Similar set calls for the remaining fields
        ps.executeUpdate();
        for (int j = 0; j < 5; j++) {
            ps2.clearParameters();
            ps2.setBigDecimal(1, ...);
            // Similar set calls for the remaining fields
            ps2.executeUpdate();
        }
    } // else curDate is a weekend and it is skipped
    curDate.setTime(curDate.getTime() + msPerDay);
}

If the start and end dates represent the year 2013, this loop will insert 261 rows into the STOCKPRICE table (via the first call to the executeUpdate() method) and 1305 rows into the STOCKOPTIONPRICE table (in the inner for loop). In the default autocommit mode, that means 1566 separate transactions, which will be quite expensive.

Better performance will be achieved if autocommit mode is disabled and an explicit commit is performed at the end of the loop:

Connection c = DriverManager.getConnection(URL, user, pw);
c.setAutoCommit(false);
...
while (!curDate.after(endDate)) {
    ...
}
c.commit();

From a logical point of view, that likely makes sense as well: the database will end up with either an entire year’s worth of data, or no data.

If this loop is repeated for multiple stocks, there is a choice of committing all the data at once or committing a year’s worth of data at a time:

Connection c = DriverManager.getConnection(URL, user, pw);
c.setAutoCommit(false);
for (int i = 0; i < numStocks; i++) {
    curDate = startDate;
    while (!curdate.after(endDate)) {
        ...
    }
    //c.commit();   // To commit a year at a time
}
c.commit();    // To commit all the data at once

Committing all the data at once offers the fastest performance, which is why the other option is commented out. In this example, though, there is a reasonable case where the application semantics might dictate that each year of data be committed individually. Sometimes, other requirements intrude on attempts to get the best performance.

Each time the executeUpdate() method is executed in the above code, a remote call is made to the database and some work must be performed. In addition, some locking will occur when the updates are made (to ensure, at least, that another transaction cannot insert a record for the same symbol and date). The transaction handling can be further optimized in this case by batching the inserts. When inserts are batched, the JDBC driver holds them until the batch is completed; then all statements are transmitted in one remote JDBC call.

Here is how batching is achieved:

for (int i = 0; i < numStocks; i++) {
    while (!curdate.after(endDate)) {
        ...
        ps.addBatch();  // replaces executeUpdate() call
        for (int j = 0; j < 5; j++) {
            ...
            ps2.addBatch();  // replaces executeUpdate() call
        }
    }
}
ps.executeBatch();
ps2.executeBatch();
c.commit();

The code could equally well choose to execute each batch on a per-stock basis (after the while loop). Some JDBC drivers have a limitation to the number of statements they can batch (and the batch does consume memory in the application), so even if the data is committed at the end of the entire operation, the batches may need to be executed more frequently.

These optimizations can yield very large performance increases. Table 11-1 shows the time required to insert one year of data for 128 stocks (a total of 200,448 insertions).

One interesting fact about this table that is not immediately obvious: the difference between line 1 and 2 is that autocommit has been turned off and the code is explicitly calling the commit() method at the end of each while loop. The difference between lines 1 and 4 is that statements are being batched—but autocommit is still enabled. A batch is considered one transaction, which is why there is a 1-1 correspondence between database calls and commits. In this example, then, a larger benefit accrued from batching than from explicitly managing the transaction boundaries.

The second factor affecting transaction performance concerns the scalability of the database as data within transactions is locked. Locking protects data integrity; in database terms, it allows one transaction to be isolated from other transactions. JDBC and JPA support the four major transaction isolation modes of databases, though they differ in the way the accomplish that.

Isolation modes are briefly covered here, though since programming to a correct isolation mode isn’t really a Java-specific issue, you are urged to consult a database programming book for more information.

The basic transaction isolation modes (in order from most- to least-expensive) are:

Databases operate in a default mode of transaction isolation: MySQL starts with a default of TRANSACTION_REPEATABLE_READ; Oracle and DB2 start with a default of TRANSACTION_READ_COMMITTED; and so on. There are lots of database-specific permutations here. DB2 calls their default transaction mode CS (for cursor stability) and has different names for the other three JDBC modes. Oracle doesn’t support either TRANSACTION_READ_UNCOMMITTED or TRANSACTION_REPEATABLE_READ.

When a JDBC statement is executed, it uses the database’s default isolation mode. Alternately, the setTransaction() method on the JDBC connection can be called to have the database supply the necessary transaction isolation level (and if the database doesn’t support the given level, the JDBC driver will either thrown an exception, or silently upgrade the isolation level to the next strictest level it supports).

For simple JDBC programs, this is sufficient. More commonly—and particularly when used with JPA—programs may want to mix isolation levels on data within a transaction. In an application that queries my employee information so as to ultimately give me a large raise, access to my employee record must be protected: that data needs to be treated as TRANSACTION_REPEATABLE_READ. But that transaction is also likely to access data in other tables, such as the table that holds my office ID. There is no real reason to lock that data during the transaction, so access to that row could certainly operate as TRANSACTION_READ_COMMITTED (or possibly even lower).

JPA allows you to specifying locking levels on a per-entity basis (and of course an entity is [at least usually] simply a row in the database). Because getting these locking levels correct can be quite difficult, it is easier to use JPA rather than performing the locking in JDBC statements. Still, it is possible to use different locking levels in JDBC applications, employing the same pessimistic and optimistic locking semantics that JPA uses (and if you’re not familiar with those semantics, this example should serve as a good introduction to them).

At a JDBC level, the basic approach is to set the isolation level of the connection to TRANSACTION_READ_UNCOMMITTED, and then to lock explicitly only that data which needs to be locked during the transaction.

Connection c = DriverManager.getConnection();  // Or...get it from a local pool
c.setAutoCommit(false);
c.setTransactionIsolation(TRANSACTION_READ_UNCOMMITTED);
PreparedStatement ps1 = c.prepareStatement(
    "SELECT * FROM employee WHERE e_id = ? FOR UPDATE");
... process info from ps1 ...
PreparedStatement ps2 = c.prepareStatement(
   "SELECT * FROM office WHERE office_id = ?");
... process info from ps2 ...
c.commit();

The ps1 statement establishes an explicit lock on the employee data table—no other transaction will be able to access that row for the duration of this transaction. The SQL syntax to accomplish that is non-standard. You must consult your database vendor’s documentation to see how to achieve the desired level of locking, but the common syntax is to include the FOR UPDATE clause. This kind of locking is called pessimistic locking. It actively prevents other transactions from accessing the data in question.

Locking performance can often be improved by using optimistic locking—the same way that the java.util.concurrent.atomic package approaches uncontended atomic operations. If the data access is uncontended, this will be a significant performance boost. If the data is even slightly contended, however, the programming becomes more difficult.

In a database, optimistic concurrency is implemented with a version column. When data is selected from a row, the selection must include the desired data plus a version column. To select information about me, I could issue the following SQL:

SELECT first_name, last_name, version FROM employee WHERE e_id = 5058;

This query will return my names (Scott and Oaks) plus whatever the current version number is (say, 1012). When it comes time to complete the transaction, the transaction updates the version column:

UPDATE employee SET version = 1013 WHERE e_id = 5058 AND version = 1012;

If the row in question requires repeatable read or serialization semantics, this update must be performed even if the data was only read during the transaction—those isolation levels require locking read-only data used in a transaction. For read committed semantics, the version column only needs to be updated when other data in the row is also updated.

Under this scheme, if two transactions use my employee record at the same time, each will read a version number of 1012. The first transaction to complete will successfully update the version number to 1013 and continue. The second transaction will not be able to update the employee record—there is no longer any record where the version number is 1012, and so the SQL update statement will fail. That transaction will get an exception and be rolled back.

This highlights to a major difference between optimistic locking in the database and Java’s atomic primitives: in database programming, when the transaction gets that exception, it is not (and cannot be) transparently retried. If you are programming directly to JDBC, the commit() method will get an SQLException in JPA, your application will get an OptimisticLockException when the transaction is committed.

Depending on your perspective, this is either a good or a bad thing. When the performance of the atomic utilities (which did transparently retry the operation) was discussed, we observed that performance in highly-contended cases could suffer when there were a lot of retries chewing up a lot of CPU resources. In a database, that situation is far worse, since the code executed in a transaction is far more complicated than simply incrementing the value held in a memory location. Retrying a failed optimistic transaction in the database has a far greater potential to lead to a never-ending spiral of retries. Plus, it is often infeasible to determine automatically what operation(s) to retry.

So not retrying transparently is a good thing (and often the only possible solution), but on the other hand, that does mean the application is now responsible for handling the exception. The application can choose to retry the transaction (maybe only once or twice); it can choose to prompt the user for different data; or it can simply inform the user that the operation has failed. There is no one-size-fits-all answer here.

Optimistic locking works best, then, when there is very little chance of a collision between two sources. Think of a joint checking account: there is a slight chance that my husband and I may be in different parts of the city withdrawing money from our checking account at exactly the same time. That would trigger an optimistic lock exception for one of us. Even if that does happen, asking one of us to try again is not too onerous, and now the chance of an optimistic lock exception is virtually nil (or so I would hope; let’s not address how frequently we make ATM withdrawals). Contrast that scenario to something involving the sample stock application. In the real world, that data is updated so frequently that locking it optimistically would be quite counter-productive.^[72]

To read less data, specify that the field in question is loaded lazily. When an entity is retrieved, the fields with a lazy annotation will be excluded from the SQL used to load the data. If the getter of that field is ever executed, it will mean another trip to the database to retrieve that piece of data.

It is rare to use that annotation for simple columns of basic types, but consider using it if the entity contains large BLOB- or CLOB-based objects.

@Lob
@Column(name = "IMAGEDATA")
@Basic(fetch = FetchType.LAZY)
private byte[] imageData;

In this case, the entity is mapped to a table storing binary image data. The binary data is large, and the example assumes it shouldn’t be loaded unless it is actually needed. Not loading the unneeded data in this case serves two purposes: it makes for faster SQL when the entity is retrieved, and it saves a lot of memory, leading to less GC pressure.

Note also that the lazy annotation is, in the end, only a hint to the JPA implementation. The JPA implementation is free to request that the database eagerly load that data anyway.

On the other hand, perhaps some other data should be preloaded—for example, when one entity is fetched, data for other (related) entities should also be returned. That is known as eager fetching, and it has a similar annotation:

@OneToMany(mappedBy="stock", fetch=FetchType.EAGER)
private Collection<StockOptionPriceImpl> optionsPrices;

By default, related entities are already fetched eagerly if the relationship type is @OneToOne or @ManyToOne (and so it is possible to apply the opposite optimization to them: mark them as FetchType.LAZY if they are almost never used).

This also is just a hint to the JPA implementation, but it essentially says that any time a stock price is retrieved, make sure also to retrieve all related option prices. Beware here: a common expectation about eager relationship fetching is that it will employ a JOIN in the generated SQL. In typical JPA providers, that is not the case: they will issue a single SQL query to fetch the primary object, and then one or more SQL commands to fetch any additional, related objects. From a simple find() method, there is no control over this: if a JOIN statement is required, you will have to use a query and program the JOIN into the query.

JPQL doesn’t allow you to specify fields of an object to be retrieved. Take the following JPQL query:

Query q = em.createQuery("SELECT s FROM StockPriceImpl s");

That query will always yield this SQL statement

SELECT <enumerated list of non-LAZY fields> FROM StockPriceTable

If you want to retrieve fewer fields in the generated SQL, you have no option but to mark them as lazy. Similarly, for fields that are marked as lazy, there is no real option for fetching them in a query.

If there are relationships between entities, the entities can be explicitly joined in query in JPQL, which will retrieve the initial entities and their related entities in one shot. For example, in the stock entities, this query can be issued:

Query q = em.createQuery("SELECT s FROM StockOptionImpl s " +
                         "JOIN FETCH s.optionsPrices");

That results in an SQL statement similar to:

SELECT t1.<fields>, t0.<fields> FROM StockOptionPrice t0, StockPrice t1
WHERE ((t0.SYMBOL = t1.SYMBOL) AND (t0.PRICEDATE = t1.PRICEDATE))

Query q = em.createQuery("SELECT s FROM StockOptionImpl s");
q.setQueryHint("eclipselink.join-fetch", "s.optionsPrices");

The exact SQL will differ among JPA providers (this example is from Eclipselink), but this is the general process.

Join fetching is valid for entity relationships regardless of whether they are annotated as eager or lazy. If the join is issued on a lazy relationship, the lazily-annotated entities that satisfy the query are still retrieved from the database, and if those entities are later used, no additional trip to the database is required.

When all the data returned by a query using a join fetch will be used, then the join fetch often provides a big improvement in performance. However, a join fetch also interacts with the JPA cache in unexpected ways. An example of that is shown in the section on caching; be sure you understand those ramifications before writing custom queries using join fetch.

JPA queries are handled like a JDBC query yielding a result set: the JPA implementation has the option of getting all the results at once, getting the results one at a time as the application iterates over the query results, or getting a few results at a time (analogous to how the fetch sized worked for JDBC).

There is no standard way to control this, but JPA vendors have proprietary mechanisms to set the fetch size. In Eclipselink, a hint on the query specifies the fetch size:

q.setHint("eclipselink.JDBC_FETCH_SIZE", "100000");

Hibernate offers a custom @BatchSize annotation instead.

If a very large set of data is being processed, the code may need to page through the list returned by the query. This has a natural relationship to how the data might be displayed to the user on a web page: a subset of data is displayed (say 100 rows), along with “next” and “previous” page links to navigate (page) through the data.

This is accomplished by setting a range on the query:

Query q = em.createNamedQuery("selectAll");
query.setFirstResult(101);
query.setMaxResults(100);
List<? implements StockPrice>  = q.getResultList();

This returns a list suitable for displaying on the second page of the web application: items 101 - 200. Retrieving only the range of data needed will be more efficient than retrieving 200 rows and discarding the first 100 of them.

Note that this example uses a named query (the createNamedQuery() method) rather than an ad hoc query (the createQuery() method). In many JPA implementations, named queries are faster: the JPA implementation will almost always use a prepared statement with bind parameters, utilizing the statement cache pool. There is nothing that prevents JPA implementations from using similar logic for unnamed, ad hoc queries, though implementing that is more difficult, and the JPA implementation may simply default to creating a new statement (i.e., a Statement object) each time.

In the sample code, the stock prices are loaded via a named query. In the default case, this simple query is executed to load the stock data:

@NamedQuery(name="findAll",
    query="SELECT s FROM StockPriceImpl s ORDER BY s.id.symbol")

The StockPrice class has a @OneToMany relationship with the StockOptionPrice class using the optionsPrices instance variable:

@OneToMany(mappedBy="stock")
private Collection<StockOptionPrice> optionsPrices;

@OneToMany relationships are loaded lazily by default. Table 11-3 shows the time to execute this loop.

The first time the sample loop is executed in this scenario (for 128 stocks with one year of data), the JPA code executes 1 SQL statement in the call to the executeQuery() method. That statement is executed at SQL Call Site #1 in the code listing.

As the code loops through the stock and visits each collection of option prices, JPA will issue SQL statements to retrieve all the options associated with the particular entity (that is, it retrieves the entire collection for one stock/date combination at once). This occurs at SQL Call Site #2, and it results in 33,408 individual select statements during execution (261 days * 128 stocks).

That example takes almost 62 seconds for the first execution of the loop. The next time that code is executed, it takes only 3.2 seconds. That’s because the second time the loop is executed, the only SQL that is executed is the named query. The entities retrieved via the relationship are still in the L2 cache, so no database calls are needed in that case.^[74]

The second line in Table 11-3 represents the code that does not visit each of the options in the relationship (i.e., the processOptions variable is false). In that case, the code is substantially faster: it takes 5.6 seconds for the first iteration of the loop, and 2.8 seconds for subsequent iterations.^[75]

In the next two experiments, the relationship between the stock prices and option prices is redefined so that the option prices are loaded eagerly.

When all the data is used (i.e., the first lines in Table 11-3 and Table 11-4), the performance of the eager and lazy loading cases is essentially the same. But when the relationship data isn’t actually used (the second lines in each table), the lazy relationship case saves some time—particularly on the first execution of the loop. Subsequent executions of the loop don’t save time since the eager loading code isn’t reloading the data in those subsequent iterations; it is loading data from the L2 cache.

As discussed in the previous section, the query could be written to explicitly use a join statement:

@NamedQuery(name="findAll",
    query="SELECT s FROM StockPriceEagerLazyImpl s " +
    "JOIN FETCH s.optionsPrices ORDER BY s.id.symbol")

Using that named query (with full traversal) yields the data in Table 11-5.

The first time the loop is executed with a join query, there is a big performance win: it takes only 17.9 seconds. That is the result of issuing only one SQL request, rather than 33,409 of them.

Unfortunately, the next time the code is executed, it still needs that one SQL statement statement, since query results are not in the L2 cache. Subsequent executions of the example take 11.4 seconds—because the SQL statement that is executed has the JOIN statement and is retrieving over 200,000 rows of data.

If the JPA provider implements query caching, this is clearly a good time to use it. If no SQL statements are required during the second execution of the code, only 1.1 seconds is required on the subsequent executions. Be aware that query caching works only if the parameters used in the query are exactly the same each time the query is executed.

If entities are never retrieved via a query, then after an initial warmup period, all entities can be accessed through the L2 cache. The L2 cache can be warmed up by loading all entities, so slightly modifying the previous example gives this code:

EntityManager em = emf.createEntityManager();
ArrayList<String> allSymbols = ... all valid symbols ...;
ArrayList<Date> allDates = ... all valid dates...;
for (String symbol : allSymbols) {
    for (Date date = allDates) {
        StockPrice sp = em.find(StockPriceImpl.class, new StockPricePK(symbol, date);
        ... process sp ...
        if (processOptions) {
            Collection<? extends StockOptionPrice> options = sp.getOptions();
            ... process options ...
        }
    }
}

The results of executing this code are given in Table 11-6.

The first execution of this loop requires 66,816 SQL statements: 33,408 for the call to the find() method, and an additional 33,408 for the call to the getOptions() method. Subsequent executions of that code are the fastest possible, since all the entities are in the L2 cache, and no SQL statements need be issued.

Recall that in the sample database, there are five option prices for every date and symbol pair, or a total of 167,040 option prices for 128 stocks over one year of data. When the five stock options for a particular symbol and date are accessed via a relationship, they can all be retrieved at once. That’s why only 33,408 SQL statements are required to load all the option price data. Even though multiple rows are returned from those SQL statements, JPA is still able to cache the entities—it is not the same things as executing a query. If the L2 cache is warmed up by iterating through entities, don’t iterate through related entities individually—do that by simply visiting the relationship.

As code is optimized, you must take into account the effects of the cache (and particularly the L2 cache). Even if you think you could write better SQL than what JPA generates (and hence should use complex named queries), make sure that code is worthwhile once the cache comes into play. Even if it seems that using a simple named query will be faster to load data, consider what would happen in the long run if those entities were loaded into the L2 cache via a call to the find() method.

Like all cases where objects are reused, the JPA cache has a potential performance downside: if the cache consumes too much memory, it will cause GC pressure. This may require that the cache be tuned to adjust its size, or that you control the mode in which entities remain cached. Unfortunately, these are not standard options, so you must perform these tunings based on which JPA provider you are using.

JPA implementations typically provide an option to set the size of the cache, either globally or on a per-entity basis. The latter case is obviously more flexible, though it also requires more work to determine the optimal size for each entity. An alternative approach is for the JPA implementation to use soft and/or weak references for the L2 cache. Eclipselink, for example, provides five different cache types (plus additional deprecated types) based on different combinations of soft and weak references. That approach, while potentially easier than finding optimal sizes for each entity, still requires some planning—in particular. recall from Chapter 7 that weak references do not really survive any GC operation and are hence a questionable choice for a cache.

If a cache based on soft or weak references is used, the performance of the application will also depend on what else happens in the heap. The examples of this section all used a large heap so that caching the 200,448 entity objects in the application would not cause issues with the garbage collector. Tuning a heap when there are large JPA L2 caches is quite important for good performance.