|
This Issue
This issue discuss connection pooling with ASP, ISAPI, IDC, and Visual Basic applications. Included is a discussion of ODBC 3.0 and the newest bug fix for ODBC. We first addressed this connection pooling in the Dec 10, 1996 issue of 15 Seconds, way before IIS 3.0 and the complication of Active Server pages.
What is Connection Pooling?
A connection pool is a set of database connections that are available for an application to use. Connection Pooling is the concept of using a connection pool for enhanced performance.
Why Use Connection Pooling?
Connection pooling is used to enhance the performance of executing commands on a database. Before executing a command a connection to that database needs to be established. Sometimes creating and tearing down the connection is more costly then executing the command. For this reason connection pools are created to keep connections alive. After a connecction is created it is placed in the connection pool. Connections are then used from the pool so that a new connection does not need to be established. If all the connections in the pool are being used, new connections are created and made avialable through the pool.
Connection pooling is extremely useful when used with applications that do not have a state. State is a presence between instances. Active Server pages are consider stateless since they do not share data between themselves. Stateless applications benefit from connection pooling since they can not hold a connection open by themselves.
Connection pooling is also usefull for sharing resources. An application can keep a connection alive, can reusing the connection multiple times to execute commands without connection pooling. But, if that application has many instances, the available resources on the machine to handle the connections will be reduced. If that application uses connection pooling, the connection resources are shared between instances of the application. With connection pooling, less connection resources are needed.
Client Server
When you think about connection pooling in the scope of client-server technology, connection pooling is handled on the server. The reason is that a client, an application, would not be able to share connection resources with other applications if each application had it's own connection pool. For this reason connection pooling must be controlled at a single point on the machine. This single point becomes a connection pooling server to all the applications. ODBC 3.0 has taken on the role of the connection server by supporting connection pooling. In another frame of reference, ODBC 3.0 is a client and the datasources are the servers. The difference with connection pooling is that ODBC 3.0 is a single client to the datasources, whereas without connection pooling, ODBC 3.0 becomes multiple clients, one for each connection to a datasource.
ODBC 3.0
ODBC 3.0 does connection pooling. Because ODBC 3.0 does connection pooling, any database interface that uses ODBC 3.0 can also take advantage of connection pooling. For instance, currently the Microsoft OLEDB provider for ODBC uses ODBC 3.0 to connect to data sources. This means that OLEDB can take advantage of ODBC 3.0 connection pooling. ADO uses this OLEDB provider to connect to Datasources. Both ADO and this OLEDB provider can take advantage of ODBC 3.0's connection pooling.
ODBC controls the number of connections pooled and times out a connection based on a setting that can be controlled through ODBC APIs.
Bug Fixes
ODBC 3.0 Version 3.00.2301 was released with a bug that might effect your web site if you are using connection pooling. Since ODBC doesn't close a connection in the pool if the connection is constantly being requested (as is the case with a high volume web site), a bad connection is not dropped. This bug turns up when you have a high volume web site and you have a bad connection. ODBC will continue to use the bad connection until there is a pause in the requests and the connection has a chance to timeout. Fortunately there is a fix to this problem with a later release.
ODBC 3.0 Version 3.00.2822
Understanding Per Processing Pooling
Connection pooling is turned on or off on a per process basis. If ODBC 3.0 connection pooling is turned on for a particular process, all applications running in that process are using connection pooling. This is very important to remember for IIS 3.0. IIS 3.0 runs all ISAPI applications in the same process. Since Active Server Pages is an ISAPI application, it runs in the same process as all ISAPI extensions and filters. This means is you have connection pooling turned on for Active Server Pages, all your other ISAPI applications will be using connection pooling. This also means that you have a chance, if connection pooling is configured per driver, that your Active Server pages and ISAPI applications will share the same connection pool.
Understanding Environment and Driver Implementations
ODBC connection pooling can be implemented two different ways. The pool can span the scope of the ODBC driver, or the scope of the ODBC enviorment. If connection pool is related to the ODBC driver, then all connections to that driver are pooled no matter what process the application is running in. This means if there are several applications using the same connection configuration going to the SQL Server for example, those connections would be sharing a connection pool if connection pooling is enabled. If a connection pool is related to the enviroment, then each application uses a different connection pool, since each application has a different enviroment.
Connection pooling within the scope of the driver is perferred since the perferred technique is to create and destroy multiple enviroments within the runtime of the application. If the connection pool was within the scope of the environment, then the connection pool would be destroyed with each destruction of the enviroment. Destroying the connection pool defeats the purpose of connection pooling. Within the application, it is preferred to create and destroy enviroments, becuase the other option is to have a global enviroment variable, and global variables are a problem with multi-threaded applications.
However, when there are many applications going to different data sources, such that the connection configuration is not the same, the connection pool grows very large when it is configured within the scope of the driver. The driver must add a separate connection for each configuration across only one driver pool. In this scenario it is better to configure the application process to pool connections within the scope of the enviroment.
Connection Pool Timeout
The default timeout value for connection pooling is 60 seconds. You can change this value for an ODBC driver by creating a registry key with the following settings:
\HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\driver-name\CPTimeout = timeout
(REG_SZ, units are in seconds)
For example, the following key sets the connection pool timeout to 30 seconds
for the SQL Server driver.
\HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\SQL Server\CPTimeout = 30
The following key sets the connection pool timeout to 10 seconds for the
Microsoft Access Driver.
\HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\Microsoft Access Driver (*.mdb)\CPTimeout = 10
Using ADO within Active Server Pages
Since ADO indirectly uses ODBC 3.0, you can use connection pooling in your Active Server pages. To use connection pooling with ASP, use ADO objects as your database interface and have connection pooling turned on for IIS 3.0.
Turning Connection Pooling On
ODBC 3.0 connection pooling automatically handles the connection pool if the code within the process turns connection pooling on. Connection pooling must be turned on before the code make any other ODBC calls. Connection pooling can only be turned on programmatically by making an ODBC API call, there are no API calls in OLEDB, nor any ADO method calls to turn connection pooling on. You can think of ODBC 3.0 as the server and OLEDB/ADO as the client. Since connection pooling is a server-side property, the code must turn connection pooling on through the server. To turn ODBC connection pooling on the code must make this API call, the example is in C:
SQLSetEnvAttr(NULL,SQL_ATTR_CONNECTION_POOLING, (void *)SQL_CP_ONE_PER_DRIVER, 0);
Turning on Connection Pooling
In order for connection pooling to be enabled with each application that application must call the ODBC driver before any other ODBC calls are made. IIS is no exception, by setting a registry setting to can indicate to IIS that it should turn connection pooling on when it starts up. You can turn connection pooling on for all ISAPI applications, including ASP, by setting this registry key to 1.
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\W3SVC\ASP\Parameters\StartConnectionPool
You will need to stop the IIS Server and restart the server before the changes to the registry take effect.
By default, connection pooling in IIS 3.0 is turned off (set to 0). Notice here that connection pooling is enabled on the server not the client (in this case the Active Server page).
Installing ODBC 3.0
The most current version of ODBC 3.0 can be installed from http://www.microsoft.com/odbc/. This version includes the bug fix mentioned above. NT Service Pack 3 also includes this version of ODBC 3.0.
ODBC's Influence
Currently the only OLEDB provider using ODBC is Microsoft's OLEDB provider, it can take advantage of ODBC connection pooling. Because ADO uses OLEDB, ADO can also take advantage of ODBC connection pooling. Active Server pages can also use connection pooling if they use ADO as their database interface
Active Server Pages Effect on ISAPI Applications
ISAPI applications run within the same process as the IIS 3 .0. This means that all ISAPI applications share the same connection pooling configurations, since the connection pool is activated per process. Either all of the ISAPI applications use connection pooling or all of them don't.
Since Active Server page is an ISAPI application, if you have connection pooling enabled for Active Server pages then all the other ISAPI applications have connection pooling enabled.
Other Methods
One method to create persistent database connections would be to create a database connection for each user and store the connection Session object. However, because this method increases the number of idle connections to the database it is recommended only for low-traffic web sites. Notice that in this technqiue, connections are not pooled, they are held open by the Session until it times out or is closed. This means that there will be one connection for every active session.
Visual Basic
Unlike Active Server Pages, Visual Basic applications, that do not run within the process of IIS, must turn on ODBC connection pooling for there process.
Connection Pooling within Visual Basic Applications
If you wish to turn on connection pooling within Visual Basic Applications that use RDO, DAO, or ADO, you must make an API call to the odbc32.dll. Here is the Visual Basic Code to enable ODBC connection pooling:
Private Declare Function SQLSetEnvAttr Lib "odbc32.dll" (ByVal henv As
Any, ByVal Attr As Long, ByVal ValPtr As Any, ByVal StrLen As Long) As
Long
Private Const SQL_ATTR_CONNECTION_POOLING As Long = 201
Private Const SQL_CP_OFF As Long = 0
Private Const SQL_CP_ONE_PER_DRIVER As Long = 1
Private Const SQL_CP_ONE_PER_HENV As Long = 2
Dim ReturnCode As Long
ReturnCode = SQLSetEnvAttr(vbNullString, SQL_ATTR_CONNECTION_POOLING,
SQL_CP_ONE_PER_DRIVER, 0)
This code needs to be called before any connections are created. You do not have to make this call from within an Active Server page.
Internet Database Connector
The Internet database connector has it's own implementation of connection pooling that does not use ODBC 3.0 connection pooling. The IDC has been using connection pooling before ODBC 3.0 was released, because the IDC has it's own connection pool, it is not effecedt the same way other ISAPI applications are effected within the process space of IIS. Other applications can not take advantage of the the IDCs connection pool since the connection pool is controlled within the IDC application and not a common server resource.
Turning On IDC Connection Pooling
To turn on connection pooling for all IDC files you will need to create this key in the registry:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\W3SVC\Parameters\PoolIDCConnections
as a REG_DWORD. Setting the key value to 1 will pool all connections by default, setting it to zero will not pool all connections.
You can also turn connection pooling on for individuals files by adding this line in each .idc file.
ODBCConnection: pool
The above line will enable connection pooling no matter what the above registry setting is set to. If you have set the registry to pool all connections, equal to 1, you can disable a single file by adding this line:
ODBCConnection: nonpool
Timing out the connection pool
Just like the ODBC connection pool, you can set the amount of time a connection in the pool exists in an inactive state. The connection time out can be set by creating this key:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\W3SVC\Parameters\PoolIDCConnectionsTimeOut
as a REG_DWORD. Set the value to the number of seconds that you want the connection to exist. By default without creating the key the setting is 30 seconds.
|
