Connection Pooling

All of this talk of dropping connections and reopening them brings us to the subject of connection pooling. Connection pooling, not to be confused with session pooling, allows connections to a database to be reused once they have been finished with. This happens automatically and, if your OLE DB provider supports it and it is enabled, you need take no action to take advantage of connection pooling. There is a single reason why you would want to pool your connections: performance. The problem with database connections is that it can take time to establish a connection. In a desktop database such as Access, this is typically a small amount of time. In a client/server database such as Oracle used on a network, this time could be measured in seconds. Given such an expensive (in performance terms) resource, it makes sense to promote its reuse. With ADO's connection pooling enabled, ADO Connection objects are placed in a pool when the application "destroys" them. Subsequent attempts to create an ADO connection will automatically search the connection pool for a connection with the same connection string. If a suitable connection is found, it is reused; otherwise, a new connection is created. The connections themselves stay in the pool until they are reused, the application closes, or they time out. By default, connections will time out after 60 seconds, but from MDAC 2.5 onward you can set this using the HKEY_CLASSES_ROOT\CLSID\<ProviderCLSID>\SPTimeout registry key. The connection pooling process occurs seamlessly, without the intervention or knowledge of the developer. This process is similar to the BDE's database pooling under Microsoft Transaction Server (MTS) and COM+, with the important exception that ADO performs its own connection pooling without the aid of MTS or COM+.

By default, connection pooling is enabled on all of the MDAC OLE DB providers for relational databases (including SQL Server and Oracle) with the notable exception of the Jet OLE DB Provider. If you use ODBC you should choose between ODBC's connection pooling and ADO's connection pooling, but you should not use both. From MDAC 2.1 on, ADO's connection pooling is enabled and ODBC's is disabled.

Connection pooling does not occur on Windows 95 regardless of the OLE DB provider.

To be truly comfortable with connection pooling, you will need to see the connections getting pooled and timed out. Unfortunately, there are no adequate ADO connection pool spying tools available at the time of writing, so we will use SQL Server's Performance Monitor as it can accurately spy on SQL Server database connections. Figure 16.7 is a look at SQL Server's Performance Monitor with all of the "counters" deleted except User Connections. This allows us to concentrate of the subject of connection pooling.

The Last field under the graph shows us the number of active connections to the database.

To see how connection pooling works, you can set up a very simple test. Create a new application and add a TADOConnection to the form. Set the ConnectionString to use the SQL Server OLE DB Provider and the Northwind database but leave Connected as False. Now add a check box with the following OnClick event:

procedure TForm1.CheckBox1Click(Sender: TObject);


ADOConnection1.Connected := CheckBox1.Checked;


FIGURE 16.7:

SQL Server's Performance Monitor

FIGURE 16.7:

SQL Server's Performance Monitor

Delphi Sql Monitor

Run the program and make sure that you can see the Performance Monitor at the same time. Now click the check box to open the connection. In the Performance Monitor, you will see the connection count increase by one. Now close the application and the count immediately decreases by one, because the connection pool is destroyed with the application. Now rerun the program, check the check box, and check it a second time to close the connection. You will see that the connection count does not decrease by one. Observe Performance Monitor for a further 60 seconds, and the connection count will then decrease by one when the pooled connection times out.

You can enable or disable connection pooling either in the Registry or in the connection string. The key in the Registry is OLEDB_SERVICES and can be found at HKEY_CLASSES_ROOT\ CLSID\<ProviderCLSID>. It is a bit array that allows you to disable several OLE DB services, including connection pooling, transaction enlistment, and the cursor engine. To disable connection pooling using the connection string, include ";OLE DB Services=-2" at the end of the connection string. To enable connection pooling for the Jet OLE DB Provider, you can include ";OLE DB Services=-1" at the end of the connection string, which enables all OLE DB services.

Was this article helpful?

0 0
Self Publishing a Book The Easy Way

Self Publishing a Book The Easy Way

This comprehensive guide will present you with a variety of self-publishing options and explore their viability. Well take a look at all types of books and book-delivery systems.

Get My Free Ebook

Post a comment