Bitter sessions in Java

Encode DataMatrix in Java Bitter sessions

You can also see that pulling back a single page of data using the vendorspecific SQL extension performed many times faster than sticking to the scrollable ResultSet. In fact, note that pulling back a page of data using the vendor s extension barely took longer than running the query itself. Pitting one approach pulling back a single page using the vendor s SQL against another pulling back the entire result set upon the first request the user will have to view more than 13 pages of data to make up for the performance cost incurred on the first request. We know from usage statistics from our applications that users typically stop at one or two pages before executing a new query or moving on. In this scenario, if you decide to go with the implementation that pulls back all data up front, you will risk memory-based scalability issues. In addition, the user will have to wait an inordinate amount of time to receive a response to their first request, quite possibly the only one they really care to see. Mini-antipattern: Paging with a Scrollable ResultSet The JDBC 2.0 API supports the notion of a scrollable result set. In earlier implementations, developers were forced to synchronously iterate a ResultSet from beginning to end. Now they can jump to random, absolute positions. Using a scrollable result set to implement a page-by-page iterator may seem like a tempting, database vendor-independent approach. Be careful. In practice, scrollable ResultSet implementations aren t all they re cracked up to be. A typical scrollable ResultSet implementation actually behaves more like a caching result set. A developer can jump to a random position in the result set but, behind the scenes, another action occurs. Rather than moving the database cursor to the absolute position, the implementation actually scrolls to the absolute position, pulling back and caching each row along the way. When using a page-by-page iterator, this behavior is equivalent in performance to using a normal result set and simply iterating to the absolute position of the page. If we need rows 890 through 900 out of a 1,000 row result set, and we execute the query and use ResultSet.absolute(int) to scroll to row 890, then pull back 10 rows, the operation will actually result in pulling back 900 rows. This results in unacceptable performance. The alternative, of course, would be to use a vendor-specific SQL extension to limit the result set size.

The only way to truly know which implementation strategy works best for you is to test in the target environment. Network speed on the client and database ends of the application server, database vendors implementations, memory restrictions,

database indexing, and result set sizes are just a few factors that affect the performance of the Page-by-Page Iterator implementations. If you begin by running a simple test suite, you ll most likely save yourself a lot of heartache down the road not to mention the time gained and team spirit enhanced by avoiding subjective arguments between team members over which implementation will perform fastest. When performance issues do arise, you ll want to measure and find out exactly where your problem lies. They may be in the data tier. If so, you ll determine the problem, and repair it, possibly by tweaking an SQL query or the database configuration; pulling back excess data from the database to the application tier is a foul practice. You should leave this data in the database, if possible, to avoid incurring the overhead of remotely transferring.

When the time arrives for you to cache in the application tier, you must ask yourself the following questions:

How expensive is the query How big is the result set How fast is the network between the application server and the database How applicable is the data Does it apply to one user, ten percent of users, all users How long until the data expires

If the query doesn t change often, is executed repeatedly, and produces globally applicable results, you should attempt to cache the results. If the results apply to a single user or expire quickly, caching isn t much of an option. To make an educated decision, you should consider performance tests in the target environment as your best option. Again, you can speculate all you want as to how the query time compares to the time it takes to pull results back from the database, but from our experience you ll be wrong as often as you are right. You really can t know for sure until you execute at least rudimentary tests in your target environment. In deciding on a caching strategy, we ve found that we can rarely if ever get by without caching. However, we ve found a reasonably safe alternative: limiting caching to already-requested data, rather than trying to predict future data requests. The latter leads down a slippery and often wasteful slope.