Performance Tuning – JDBC to SQL Server

Today in my series on performance tuning Progress DataDirect products, I’m going to shed some light on a few of the tuning options available in our SQL Server JDBC Driver.

Performance Tuning Options

The SQL Server JDBC driver offers a wealth of options and properties that can be tuned for performance. When considering whether to use any performance tuning option, the key is to understand the benefits and tradeoffs. The Performance Considerations section of our User’s Guide contains information to help you make an educated assessment of whether a particular option will help you and whether the tradeoff is something you can manage.

Performance Considerations

Performance Tuning Wizard

The easiest way to tune any Progress DataDirect JDBC drivers is to use our Performance Tuning Wizard. While a very useful tool, it is important to answer the questions accurately to ensure optimal performance. Answering questions incorrectly, no matter how innocently, can create unexpected results. For help on how to answer a particular question, see the link above which provides information on each of the tuning options.

Using the Performance Tuning Wizard

Snapshot Serializable

Snapshot Isolation provides transaction-level read consistency and an optimistic approach to data modifications for SQL Server versions 2005 and higher. The Snapshot Serializable feature works by not acquiring locks on data until data is to be modified, which is useful if you want to consistently return the same result set even if another transaction has changed the data, and:

1. Your application executes many read operations or
2. Your application has long-running transactions that could potentially block users from reading data

This feature can eliminate data contention between read and update operations and can therefore increase performance due to increased concurrency.

To use this option, make sure that your database is configured for Snapshot Isolation. See “Using the Snapshot Isolation Level (Microsoft SQL Server 2005 and Higher)” for details.

LongDataCacheSize

Retrieving long data (typically data from TEXT or IMAGE fields) can be very performance intensive. This is due to the fact that the driver must account for the possibility that the application could request the data out of sequence with the order for which they are defined in the result set (i.e. requesting the data from column 5 before requesting it for column 3). If your application will always retrieve long data in column sequence, you can disable the driver’s caching for long data with this option. If there is a possibility that your application could retrieve long data column values out of order, then the long data values must be cached and this option will help you tune for the best possible performance.

Performance Tips

For those seeking a list of best practices or guidelines for ensuring their JDBC applications perform at their best, check out the Performance Tips document at our Developer Center.

Performance Tips

Performance Benchmark Results

Just looking for a benchmark? We offer on our website that show why our SQL Server JDBC driver is the best performing JDBC driver of its kind. In addition to example results showing driver throughput, CPU efficiency and memory usage results are also included along with details on how we run our performance tests.

Performance Benchmark Results

JDBC Performance Architecture