dcsimg

Connection Pooling

By ServerWatch Staff (Send Email)
Posted Nov 5, 1998


Scott Mitchell

If your ASP applications interact with databases at all (and I am sure they do, else why use Active Server Pages?), then you can experience a great increase in database performance if you are utilizing connection pooling. The time and resources needed to create a connection to a database are not trivial. If for every page where you create a connection instance, a new connection must be created, and then deleted once the page is finished executing, your users will notice the performance lost if there are many people hitting your server simultaneously. * This article describes how to utilize connection pooling in your ASP application.

Connection pooling keeps the connection instances around after a particular page is through using it. For example, say that you have an ASP file that creates a connection to a SQL database, retrieves some data, and displays it on the web page. Without connection pooling enabled, here are the sequence of steps that ASP performs:

Create a connection to the database Send the query to the database Get back the resultset Display the resultset Destroy the connection

Each user who hits this page will have to go through each of these steps. With connection pooling, however, the steps involved look more like this:

See if an unused connection exists in our "pool" of connections If so, use it, else create a new connection Send the query to the database Get back the resultset Display the resultset Return the connection to the "pool"

It is much less costly to reuse a connection than to have to create a brand new one each and every time a page needs to establish a connection to a database.

Now, let's look at how we can make sure our ASP applications use connection pooling. There are two steps which you need to take to utilize connection pooling: one, you must make sure your ASP registry settings have connection pooling enabled; two, you must have SQL server set up to use TCP/IP Sockets.

First, to edit the registry so that our ASP programs use connection pooling you will need to run the REGEDIT program and alter the following registry entry:

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\W3SVC\ASP\Parameters

Find the StartConnectionPool entry and change the value from 0 (zero) to 1 (one). Once this change has been made, ADO and ASP will utilize connection pooling.

The final step is to set SQL server to use TCP/IP Sockets. To set your SQL Server to use TCP/IP Sockets, you need to go into SQL Server and select the SQL Server Client Configuration Utility (via Start / Programs / Microsoft SQL Server / SQL Server Client Configuration Utility). Once the utility opens, click on the Net Library tab. Under the Default Network setting, choose TCP/IP Sockets. That's all there is to it!

Scott Mitchell

Page 1 of 1


Comment and Contribute

Your name/nickname

Your email

(Maximum characters: 1200). You have characters left.