Company Database Connection Pooling
As of Keystone 2.6.8.3, company database connection pooling has been added as an experimental feature (by default it is turned off.) To activate this feature, you need to manually edit the CCWIBSV.INI file (located in the C:\ProgramData\GivenHansco\Keystone\Settings folder) and add the following line to the end of the [IBServer] section:
PoolConnections = <n> (where <n> is the maximum number of InterBase connections the pool should use)
The Good
Why should I enable connection pooling?
Normally, when the user logs in to Keystone, or selects a Keystone menu option, a new connection must be made to the company database. Opening a connection to a database takes time--on some customer systems it can take up to 4 seconds.
Connection pooling maintains a shared "pool" of physical database connections. Once a database connection is opened by the pool, it remains open for a certain period of time (1 hour by default) even after the Keystone client that requested it has finished with it. This allows the database connection to be re-used if another Keystone client subsequently requests a new connection to that company database.
Below is a sample sequence to illustrate the benefits of connection pooling. Each opening of a physical database connection is highlighted. Remember that each connection can take as much as 4 seconds.
| Client Action | Without Pooling | With Pooling |
|---|---|---|
| Login | Open Database | Open Database & Get Connection from Pool |
| Close Database | Release Connection to Pool | |
| Open Customer File Maintenance | Open Database | Get Connection from Pool |
| Close Customer File Maintenance | Close Database | Release Connection to Pool |
| Open Product File Maintenance | Open Database | Get Connection from Pool |
| Close Product File Maintenance | Close Database | Release Connection to Pool |
| Close Menu (last user to logout) | Close Database |
This example shows that with only one user logged in to Keystone the number of times a physical database connection must be opened is greatly reduced. As the number of users increases and the number of Keystone options increases, the difference becomes even more dramatic. The user's perception is that Keystone options open almost instantly instead of taking 3 to 4 seconds to appear. (There are other factors that can add to the time it takes for a particular Keystone option to open.)
The Bad
Is there a downside to using connection pooling?
A situation where connection pooling provides little to no benefit is the case where there are many companies that are (more or less) equally active. Unless a very high number of connections are made available to the pool, it is likely that physical database connections will need to be closed/opened due to users working in different companies going into and out of Keystone options. Even so, there is very little overhead with connection pooling so it is more likely than not to provide at least some benefit.
The Ugly
What should I set the value of PoolConnections to?
To determine this, an explanation of users versus connections is in order.
InterBase licenses are for a specific number of users. Each user is allowed to make up to 4 connections to one or more databases. So a 5-user InterBase license is good for 20 database connections.
Keystone has three different types of databases:
- System - one connection per server
- Document Imaging - one connection per user per option
- Company - one connection per user per option
Additionally, to improve performance the Application Server opens one additional connection to each company database for caching item lookups.
Currently, the connection pool provides pooling for Document Imaging and Company database connections. The additional company database connection for caching is already shared and is not part of the pool.
A reasonable formula for determining the value for PoolConnections is: [number of InterBase licensed users] times 4 minus 1 minus [number of companies]