Performance Tuning – ODBC to SQL Server

The final post in my series on performance tuning Progress DataDirect products will cover performance tuning options for Connect and Connect64 for ODBC SQL Server Native Wire Protocol driver.

The first thing to note is that the SQL Server Native Wire Protocol driver is available starting with the 6.0 service pack 2 release of Connect and Connect64 for ODBC. If you are using a release prior to 6.0 service pack 2, then you will definitely want to upgrade to this release and make sure your application is pointing to this driver and not the older SQL Server Wire Protocol driver. Beyond introducing a number of significant new product features (Bulk Load, Application Failover, Kerberos, SSL, and more), you should see an immediate performance boost over previous releases – at least 35% in most scenarios!

Beyond this base-level performance boost, this driver introduces a number of new tuning options to enable users to see even better performance. Here are some tips to help you use these tuning options most effectively to boost performance even more.

Performance Tuning Options

The SQL Server Native Wire Protocol ODBC driver offers several options and properties that can be tuned for performance. The key to using them successfully is to understand the benefits and tradeoffs of each. Details are available online under the Performance Considerations section of our User’s Guide. Performance Considerations

Performance Tuning Wizard

Tuning any of our ODBC drivers is to use the Performance Tuning Wizard. Like any tool, the Performance Tuning Wizard is only useful if you know how to use it, and that starts by understanding what you are doing by using the tool to modify various connection options.  Don’t struggle with trying to figure out what the options you are modifying do – if you are unsure of how to answer a particular question, check the link above that describes each of the tuning options and try different value to see how they impact your performance. Using the Performance Tuning Wizard

Packet Size

In most cases, the optimal setting for Packet Size is to specify the maximum packet size that the database server allows. Doing so reduces the total number of network round trips required to return data to the client, thus improving performance. Set the Packet Size attribute  to the  the maximum packet size of the server initially, then modify it as needed if the packet sizes start to fragment due to the network topology and maximum packet size settings on the various servers between your application and database.

Use Snapshot Transactions (SnapshotSerializable)

Snapshot Isolation provides transaction-level read consistency and an optimistic approach to data modifications for SQL Server versions 2005 and higher. The Use Snapshot Transactions 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 “Isolation Level and Lock Levels Supported" for details.

Performance Design Tips

Looking for a general set of best practices or guidelines for ensuring that your ODBC application performs at its best? Check out the Performance Tips document at our developer center. Designing Performance - Optimized ODBC Applications

Performance Benchmark Results

Benchmark results that illustrate why our SQL Server ODBC driver is the best performing ODBC driver of its kind are also available. Our website has example results showing driver throughput, CPU efficiency, and memory usage results along with details on how we run our performance tests. Additional details on how the architecture of our ODBC driver ensures the best available performance are also available. Performance Benchmark Results ODBC Performance Architecture