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.
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.
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
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.
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:
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.
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
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
View all posts from Paul Griffin on the Progress blog. Connect with us about all things application development and deployment, data integration and digital business.
Copyright © 2018 Progress Software Corporation and/or its subsidiaries or affiliates.
All Rights Reserved.
Progress, Telerik, and certain product names used herein are trademarks or registered trademarks of Progress Software Corporation and/or one of its subsidiaries or affiliates in the U.S. and/or other countries. See Trademarks for appropriate markings.