SQL Anywhere connection pooling

Connection pooling may improve the performance of applications that make multiple, brief connections to the database server. If connection pooling is enabled for a connection, when it is disconnected, it is automatically cached and may be reused when the application reconnects. You control connection pooling with the ConnectionPool (CPOOL) connection parameter. Once an application makes a specified number of connections with the same connection string, then the connection is pooled.

An application must make five connections with the same connection string before a connection is cached. The connection name can be unique each time, but all other connection parameters must be identical for a cached connection to be reused.

If the application process connects again and there are cached connections available for the same connection string, the cached connection is reused. Connections remain in the cached state for the time specified by the ConnectionPool (CPOOL) connection parameter (60 seconds by default).

Cached connections are not reused if it would change the behavior of the application. For example, cached connections are not reused for databases that stop automatically when there are no connections to them, if connections are disabled, if the database server has reached its connection limit, or if a password has changed.

To ensure that connection pooling is transparent to the application, a connection is disconnected if a failure occurs when caching a connection. If a failure occurs when attempting to reuse a cached connection, the database server attempts to connect normally.

A connection is cached if it is disconnected and the maximum number of connections specified by the CPOOL connection parameter has not been reached. The connection is reinitialized, and the cached connection remains connected to the database server even though the application has disconnected it. The cleanup and reinitialization of a connection includes the following activities:

Rolling back all outstanding transactions.
Dropping temporary tables, temporary functions, and variables.
Resetting connection options and connection counters.
Decrementing and incrementing the database server connection counts. You are not informed that there are active connections when a database server with cached connections shuts down.
Executing all defined disconnect and connect events.
Executing the login_procedure database option and verifying the login policy.
Resetting the connection ID.

Using SQL Anywhere connection pooling with other connection pooling products

If you are using a product or API that supports connection pooling, then the connection pooling of the product or API supersedes SQL Anywhere connection pooling. Both types of connection pooling can be active at the same time.

The behavior of connection pooling in your product or the API may be significantly different than SQL Anywhere connection pooling. If the behavior of connection pooling in your product or API is inappropriate for an application, SQL Anywhere connection pooling can be used and may improve the performance of some applications.

Connection pooling and read-only scale-out

If the NodeType (NODE) connection parameter is also specified for a connection, the application typically connects to the primary server and the primary server determines which copy server is least heavily loaded. The connection is then redirected to that node. If the application makes and drops several such connections within a short period of time, the connection is pooled and the primary server is not asked which copy server to use. This behavior reduces the load on the primary server, but may not give expected behavior.