Browse DevX
Sign up for e-mail newsletters from DevX


Tuning Java Database Performance: Understanding the Role of the Driver : Page 2

Isolating performance issues in Java database applications is not a straightforward affair. Few developers know that the ability to fine-tune the JDBC driver can be just as influential as the database itself or the SQL queries that call it. Get the basics of JDBC optimization.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Dirty Reads, Nonrepeatable Reads, and Phantom Reads
Controlling data consistently through database locking generally means deciding whether to allow dirty reads, nonrepeatable reads, or phantom reads. A dirty read occurs when the results in one transaction are modified by another uncommitted transaction, which is later discarded. The data in the first transaction is invalid, or "dirty." A nonrepeatable read occurs when one transaction repeatedly retrieves data, while a different transaction alters the underlying data. This causes different, or nonrepeatable, results to be read by the first transaction. A phantom read is similar to a nonrepeatable read, except that the number of rows is changed either by insertion or deletion, so that the first transaction contains "phantom" data.

Fortunately, the JDBC specification allows developers to decide how transactions should interact by passing parameters to the appropriate constructor during the creation of Connection and ResultSet objects. Transactions also can now utilize savepoints, which reduce transaction overhead by providing checkpoints for saving or ignoring modifications during a long-running transaction. The specification also provides for batch processing and updateable ResultSet objects, which also affect performance and transaction behavior. Of course, how a JDBC driver implements these features—if it even does—greatly affects the performance of your application.

Handling Network Communication
Another area where the performance of JDBC drivers differs is in handling network communication. Primarily this arises when the JDBC driver transfers the results of a query from the database server to the Java application. Depending on the needs of the application, a developer might only want a few rows transferred, such as in a search engine, or all the rows transferred, such as in a billing service. The JDBC specification allows a developer to specify the order and number of rows that are fetched by the driver via the setFetchDirection and setFetchSize methods in the Statement interface. It is up to the driver vendor, however, in how, or even if, to implement these features.

Choosing a Vendor—Easier Than You'd Think
Using the car analogy, a car's performance can be improved by a knowledgeable mechanic. In fact, the first step taken by a good mechanic is to obtain as much diagnostic information as possible. The same is true when using a JDBC driver. The beauty of Java specifications, like JDBC, is that different vendors provide competing implementations, which can and generally do work in different ways.

By now, most developers understand that Type 4 JDBC drivers generally offer the highest performance. But how do you choose a JDBC driver? Many developers (incorrectly) assume that a database vendor provides the optimal JDBC driver; after all, they wrote the database so they should be able to write the best JDBC driver for their database. This is not the correct line of reasoning because a JDBC driver is in reality a middleware product, not a database product.

A Diagnostic Tool Helps Too
One of the most recognized anecdotes in the computer industry is the 80-20 rule. When it comes to the performance of JDBC applications, this rule states that 80 percent of performance bottlenecks are due to either the actual JDBC application or how the application interacts with the JDBC driver, while 20 percent is due directly to the JDBC driver. Telling the difference can be difficult, even for the most advanced JDBC developer. What you need is the right diagnostic information, in order to know whether your performance is hindered by your SQL statements or locking convention.

Many driver vendors also provide a variety of diagnostic tools that allows a developer to "spy" on a JDBC driver, even when it is used by other users. In this way they can see what is truly going on "under the hood," and identify bottlenecks, whether they are in the JDBC code or the driver.

Robert Brunner works at the National Center for Supercomputing Applications. He is the author of The Complete Idiot's Guide to JavaServer Pages (Alpha Press) and Java Web Services Unleashed (Sams Publishing); upcoming titles include Practical JSP (Morgan-Kaufmann). Reach him at drbrunner@mchsi.com.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date