Progress
Version 9
Product Update Bulletin


Connection Pooling

The DataServer for Microsoft SQL Server is enhanced with the ability to form a connection pool. A connection pool is a set of database connections that are available for an application to use and reuse without having to be reestablished. Connection pooling significantly improves the cursor management associated with no-lock queries, particularly multi-table joins. Creating and tearing down connections can be resource intensive. Using a pooled connection to keep existing connections alive results in significant performance gains because the DataServer avoids the overhead of making a connection for each request. 4GL applications that open multiple no-lock queries and handle their results simultaneously experience the best cumulative performance gains from connection pooling.

Connection pooling for the DataServer for Microsoft SQL Server is a combination of ODBC connection pooling and DataServer connection management. Individually, ODBC connection pooling or DataServer connection management provides the foundation required for firehose cursors, but enabling both provides the best performance. For more information on firehose cursors, see the "Firehose and Fast Forward-Only Cursors."Without a connection pool, firehose cursors will block an application until a full result set is retrieved. Because of this, when connection pooling is disabled, firehose cursors are also disabled. By maintaining multiple connections and one cursor per connection, read-only requests only block the connection on which they retrieve results, freeing the 4GL application to continue processing data on the other connections.

ODBC connection pooling and DataServer connection management provide the highest performance improvements when enabled together, but they can also be enabled independent of one another. For installations where the number of ODBC connections is limited, you can enable only the DataServer connection management. For deployments in which memory constraints are a concern, enabling only the ODBC connection pool provides on-demand connections, with optimized resource efficiency. If both an ODBC connection pool and managed connections coexist in the same DataServer process, the managed connections will come from the ODBC connection pool. The managed connections are distinguished from the connection pool connections in that they are never released back to the pool for the life of the 4GL session.

When both ODBC connection pooling and DataServer connection management are enabled, set the number of managed connections to a value that covers the required number of connections for a typical application. Reuse of the managed connections takes precedence over ODBC connection pool connections. The ODBC connection pool is then an overflow handler for connections required beyond the capacity of the managed connections. This configuration ensures that there is little risk of having a negative impact on performance by downgrading to a Fast Forward-Only server-side cursor in the mainline transaction-oriented connection. For more information on monitoring the performance of your connection pool, see the "Cursor and Connection Use Monitoring."

ODBC Connection Pooling

ODBC connection pooling is managed by the ODBC driver manager, external to the DataServer. The size of the pool grows and contracts dynamically based on demand and time-out intervals. The upper limit to the pool size is limited only by memory and other server resource constraints. The DataServer retrieves connections out of the externally managed pool. ODBC connection pooling is maintained with the CONNECTION_POOLING option to the -Dsrv connection parameter. ODBC connection pooling is enabled by default.

Disable ODBC connection pooling with the following syntax:

-Dsrv CONNECTION_POOLING,0 

NOTE: To take advantage of ODBC connection pooling in the DataServer, you must rebuild your schema holder.

DataServer Connection Management

When a 4GL session is established, the DataServer for Microsoft SQL Server opens the number of read-only connections specified in the PRGRS_CACHE_CONN value. The DataServer maintains these connections for the duration of the 4GL session, reusing them for subsequent queries.

NOTE: To take advantage of DataServer connection management, you must rebuild your schema holder.

Details About Connection Management

This section presents information about

DataServer connection management is enabled and tuned with the PRGRS_CACHE_CONN option to the -Dsrv connection parameter. Connection management is enabled by default, and the default number of managed connections is 5.

Change the number of connections using the following syntax:

-Dsrv PRGRS_CACHE_CONN,n 

where n is the number of managed connections.

In contrast, DataServer connection management is disabled by setting the number of managed connections to zero, as shown:

-Dsrv PRGRS_CACHE_CONN,0 

Connections for stored procedures are allocated from the managed connections when DataServer connection management is enabled. If your stored procedures modify the connection environment or attributes, allocate a set of managed connections exclusively for stored procedures to prevent the modified connections from being reallocated for general use.

Allocate managed connections exclusively for stored procedures with the following syntax:

-Dsrv PRGRS_SP_CACHE_CONN,n 

where n is the number of managed connections allocated to stored procedures. Set n to a value large enough to handle the largest number of stored procedures you can run simultaneously. If you exceed n, the connection will be allocated from the ODBC connection pool, if enabled, making the modified connection available for general reuse.

NOTE: This switch is off by default, and is only needed for applications that modify the connection environment or attributes within stored procedures.

Running a stored procedure in a separate connection changes the timing of changes being committed to the data source. When a stored procedure is run in a separate connection, changes not explicitly committed during the execution of the stored procedure are committed at the time the procedure handle is closed and the connection is recycled.


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095