Welcome!

PowerBuilder Authors: Chris Pollach, Yeshim Deniz, Jayaram Krishnaswamy, Kevin Benedict, Avi Rosenthal

Related Topics: PowerBuilder

PowerBuilder: Article

Jaguar Connection Caches

Jaguar Connection Caches

One of the greatest benefits of moving your distributed applications to Jaguar CTS is the ability to use Jaguar's connection caches to manage database access for all users of your application. Connection caches allow Jaguar applications to share a pool of connections. In theory, this avoids the "connection overhead" associated with establishing a connection to the database. When an application issues a disconnect statement, Jaguar doesn't actually terminate the connection to the database, it merely returns the connection to the pool for use by the next application that makes a connect statement. In theory, this can provide a substantial performance boost for applications that perform many connect/disconnect operations.

Connection caches also offer support for applications in which the developer is unaware at design time of how many users may use the application (and the database) at any given time. Internet applications certainly fall into this category because it's often difficult to gauge how many Web surfers will hit a site at any given time. If, for example, you have a database engine that's licensed for 100 concurrent connections and 110 users attempt to connect at the same time, in a noncached environment the last 10 would receive an error message telling them that they were unable to connect to the database. This is certainly not a good result in today's fast-paced, e-commerce-driven world. Those 10 users will almost certainly take their business elsewhere.

Connection caches solve the problem by efficiently sharing the 100 available connections among the hundreds or thousands of concurrent users. When the 100 connections are all in use, rather than returning a connection error to the user, Jaguar has a prespecified wait time. During this time it attempts to reconnect until a connection is available or the timeout period is exceeded.

This article demonstrates how to create a Jaguar connection cache and compares the performance of that cache to the performance of PowerBuilder native drivers on identical operations on a test database.

Creating Connection Caches
Sybase has made it easy to create and manage connection caches through the use of Sybase Central's Jaguar Manager utility shown in Figure 1.

This utility, written in Java, allows you to manage all aspects of your Jaguar server including connection cache management.

To create a connection cache for your server, expand the server tree as shown in Figure 1 and right-click on Installed Connection Caches. The resulting dialog will ask whether you want to install an already created cache to this server or create a new cache and then install it to the server (see Figure 2).

For purposes of this discussion we'll create a new cache and install it to the server in a single step by clicking on the Create and Install a New Connection Cache button. You'll be asked to enter a name for this connection cache (see Figure 3) that your application will use when establishing a connection via the cache.

Since connection cache names are case sensitive, I always recommend using all lowercase names.

Click Create New Connection Cache to create the cache and display the Connection Cache Properties dialog shown in Figure 4.

Most of the property settings here are self-evident; however, a couple of them do have some bearing on cache usage and the performance issues discussed later in this article.

Enable cache-by-name access allows your code that uses the cache to access the cache by name rather than by supplying a username and password. This is a great convenience and I always recommend it.

Enable connection sanity check allows Jaguar to validate the stability of the connection each time it's returned to the cache. If a connection isn't in a usable condition, Jaguar will remove it from the cache. This check does affect performance, but may keep your program in a stabler condition.

The number of connections in the cache sets the upper limit of the number of connections that Jaguar will hold open. This figure should be adjusted depending on system usage so that Jaguar always has available connections. When a connection is requested from the cache and one isn't available, performance will be affected.

You should also set the appropriate Server Name, User Name and Password properties for your database connection. In the case of ODBC, which we use here, the Server Name is the name of the ODBC DSN you wish to use, which is set up using the utility that appears in the Control Panel in Windows.

The Driver tab (see Figure 5) is where you specify the driver type.

For ODBC enter odbc32.dll in the DLL or Class Name edit and select the ODBC radio button. For JDBC enter the fully qualified path to the JDBC driver class you wish to use.

To test the cache, return to the General tab after saving changes and click the Ping button. Jaguar will make a connection from the cache and report on success or failure (see Figure 6).

Coding to Use the Connection Cache
Once you have the connection cache correctly configured and successfully tested, using it from a PowerBuilder Jaguar component is as easy as making a connection in PowerBuilder code. Simply create a transaction object (or use SQLCA), populate its properties with the values you need to connect, then issue a connect statement. This code is all that's needed to connect via the connection cache we just created:

SQLCA.DBMS = "ODBC"
SQLCA.Database = "JagTest"
SQLCA.AutoCommit = False
SQLCA.DBParm = " ConnectString='DSN=JagTest;UID=sa;PWD=',
CacheName='jagtest'"

connect using SQLCA;

When the connection statement is executed, the SQLCA transaction object won't attempt to connect to the database directly. Rather, it'll request a connection from the jagtest connection cache. Jaguar will handle the request and return a connection. If there are no connections currently in the cache, Jaguar will connect to the database and then return a connection.

Connection Cache: Pros and Cons
Even though they're a powerful connection management tool, connection caches do have some drawbacks. For example, all connection caches in a single pool will share a single user name and password. Jaguar will manage security for that connection to the database, but actual user authentication will require additional program coding. This has become the accepted practice for writing distributed applications; however, some developers will want to allow the database to handle the authentication and this isn't possible with connection caches.

Perhaps the severest drawback of connection caches is that they're currently limited to using ODBC or JDBC connections for non-Sybase and non-Oracle applications.

Native Drivers vs ODBC
Those of us who have programmed in PowerBuilder are familiar with the performance advantages gained by using the PowerBuilder native drivers over ODBC or JDBC. In this article we'll take a look at whether this performance hit applies to Jaguar connection caches and, if so, whether the hit is sufficient to mandate against their use in high-performance applications. It must be stressed that this test only compares PowerBuilder native drivers with a specific ODBC driver for a single database engine. No tests were run to determine whether the results hold true for JDBC, other ODBC drivers or other connection methods that support connection caches (Sybase CTLIB and Oracle OCI).

The Test Platform
To make sure that our timings reflect only the differences between using code that connects using PowerBuilder native drivers and ODBC, care must be taken to eliminate differences such as network configuration and the connection type between the client and the Jaguar server (since all the data must eventually return to the client via that connection). For that reason all tests were run on a single PC, a Dell Optiplex with a 450 MHz PII processor and 128MB of RAM, running Windows NT 4.0 with Service Pack 5. A single PowerBuilder 7.0 nvo was developed and deployed to Jaguar. Switching between the native drivers and the connection cache was handled by editing the PowerBuilder code and then redeploying the Jaguar component. The Jaguar component was stateful and maintained its connection until the client issued a specific disconnect.

The client was developed in PowerBuilder and didn't change between tests. The code for both the server and the client is available for download from www.timhatton.com.

The Test Database
The test database consisted of a single table with two columns - an integer column (intvalue), which was the primary key, and a char(40) column (charvalue).

The database engine was MS SQL Server 7.0 Desktop. The PowerBuilder native MS SQL 6 (pbmss70.dll) driver was used for the native connection and the ODBC driver, supplied by Microsoft with the installation of SQL Server 7.0 Desktop, was used for the connection cache. The cache was set to maintain 10 connections, although only one active cache was used in this test.

Operations Tested
All basic database operations were tested:

  • Insert 1,000 rows of data (all rows were identical).
  • Update 1,000 rows of data to alter the charvalue column one row at a time.
  • Delete 1,000 rows of data one row at a time.
  • Time needed to establish the first connection.
  • Time needed to establish additional connections.
The Test Client
The test client was a standard PowerBuilder application that connected to the Jaguar server and created a single instance of the n_jagtest object. The client displayed the start time, stop time and elapsed time for each operation. A sample session of the test client is shown in Figure 7.

Factoring in a Network
Once the single machine test was completed, another test was performed using the same code but with the database located on a separate physical machine and accessed across the network. This configuration more accurately mimics the configuration of most distributed application designs. The database is seldom located on the same machine as the Jaguar server. The network was a standard Windows NT 4.0 setup using 100MB Ethernet and TCP.

Times for the network test are shown in Table 2.

Test Analysis
A review of these times leads to some interesting conclusions. One of the principal benefits of connection caches is the ability to reuse an open connection. The rationale is that since connecting to the database is generally an expensive operation, allowing Jaguar to maintain a set of open connections and provide an open connection to any client that makes a connection request will result in faster connection times for all connections, except the very first. However, the data supports this conclusion only for instances in which the database is accessed across the network. On the single machine test the server returned a connection for both the native driver and the connection cache test that was within the granularity of the now() function, while in the networked configuration the connection cache connected in one second for the first connection and then zero seconds for subsequent connections.

In other words, connection times for all connections (including the first) were reported at essentially zero (one second not being a significant difference). Clearly, connection caches offer little speed advantage in returning connections faster than other methods on the test platform. However, this test should be repeated under load conditions to determine whether pending database operations might slow the connection times for native drivers or for initial connections.

Further analysis shows that all other operations were significantly slower for connection caches than they were for native drivers. This is undoubtedly due to the inherent slowness of the ODBC driver as compared to the native driver.

The worst performance hit was taken in the simple insert test in which 1,000 rows of data were written sequentially to the database. The native driver was twice as fast as the connection cache ODBC connection. Updates and deletes were each two seconds faster than the same operations using connection caches.

No changes were seen when disconnecting from the database.

As with many application architecture decisions, the correct approach will depend heavily on what your application needs to do and whether it's necessary to derive the absolute maximum performance for every operation. If performance is paramount, as it would be with a real-time data collection application, you clearly wouldn't want to use connection caches. However, if you can live with a performance slowdown in some areas (perhaps you're not performing sequential operations as we did in the test application; however, singleton inserts, updates and deletes would presumably return essentially 0), then the advantages of allowing Jaguar to manage total connections to the database and database security may make them worthwhile.

You should also consider the system usage load. If you're developing an application and are unable to determine the maximum number of concurrent users and to allocate database resources accordingly, you'll be much better off taking the connection cache hit.

The key is not to use connection caches just because they're there; analyze your project and determine whether they give you anything of value and make the decision based on that analysis.

In that way you can deliver an application that meets your performance standards as well as uses the best technology (not necessarily the newest) for the job.

More Stories By Tim Hatton

Tim Hatton, a principal in Millennium Technical Trainers, Beavercreek, Ohio, develops Java-oriented courseware and classroom curriculums.

Comments (0)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.