You are currently offline, waiting for your internet to reconnect

INFO: Connection Pool Management by ADO Objects Called From ASP

This article was previously published under Q191572
SUMMARY
When using ActiveX Data Objects (ADO) within an Active Server Pages (ASP)page, it is important to know how to utilize connection poolingeffectively. When using different combinations of implicit and explicit ADOconnections with ADO recordsets and commands instantiated with Server.CreateObject or just CreateObject, it is possible for the connections to not be returned to the connection pool. The table in the "More Information" section depicts possible scenarios and outcomes.

Note Connections that use the Jet OLE DB providers and ODBC drivers are not pooled because those providers and drivers do not support pooling.
MORE INFORMATION
By following a connection type from the left of the grid, and a recordset or command type from the top of the grid, you can find the answers to thefollowing two questions:

  • Is the ADO connection (Conn) returned to the connection pool?
  • What statement returns the connection to the pool?
                |Recordset     |Recordset     |Command      |Command                |created       |created       |created      |created                |with          |with          |with         |with                |Server.       |CreateObject  |Server.      |CreateObject                |CreateObject  |              |CreateObject |   -----------------------------------------------------------------------   Explicit     |1.Yes         |1.Yes         |1.Yes        |1.Yes   Connection   |2.Conn.Close  |2.Conn.Close  |2.Conn.Close |2.Conn.Close   created with |              |              |             |   Server.      |              |              |             |   CreateObject |              |              |             |   -----------------------------------------------------------------------   Explicit     |1.Yes         |1.Yes         |1.Yes        |1.Yes   Connection   |2.Conn.Close  |2.Conn.Close  |2.Conn.Close |2.Conn.Close   created with |              |              |             |   CreateObject |              |              |             ---------------------------------------------------------------------------   Implicit     |1.No          |1.Yes         |1.No         |1.No   Connection   |2.N/A         |2.Set         |2.N/A        |2.N/A                |              |  Recordset   |             |                |              |  = Nothing   |             |---------------------------------------------------------------------------				

Sample Code


Note You must change UID=<username> and PWD=<strong password> to the correct values before you run these codes. Make sure that UID has the appropriate permissions to perform this operation on the database.
   Explicit Connection - Server.CreateObject      sConnect="DRIVER={SQL Server};SERVER=ServerName;DATABASE=Pubs;UID=<username>;PWD=<strong password>"      Set Conn = Server.CreateObject("ADODB.Connection")      Conn.Open sConnect   Explicit Connection - CreateObject      sConnect="DRIVER={SQL Server};SERVER=ServerName;DATABASE=Pubs;UID=<username>;PWD=<strong password>"      Set Conn = CreateObject("ADODB.Connection")      Conn.Open sConnect   Implicit Connection with Recordset      sConnect="DRIVER={SQL Server};SERVER=ServerName;DATABASE=Pubs;UID=<username>;PWD=<strong password>"      Set Rs = CreateObject("ADODB.Recordset")      Rs.Open "SELECT * FROM Authors", sConnect				

How to Recreate These Tests

  1. Create an ASP page with one of the following scenarios.
          Sample ASP page:      <%@ LANGUAGE="VBSCRIPT" %>      <HTML>      <BODY>      >%      dim cn(10)      dim cmd(10)      For x = 0 to 10       Set cn(x) = Server.CreateObject("ADODB.Connection")       cn(x).Open "DRIVER={SQL Server};SERVER=Ovteam;DATABASE=Pubs;UID=<username>;PWD=<strong password>"       Set cmd(x) = Server.CreateObject("ADODB.Command")       cmd(x).activeconnection = cn(x)       cmd(x).commandtext = "SELECT * FROM Authors"       cmd(x).execute       Response.Write "Command executed: " & x & "<BR>"       Set cmd(x) = Nothing       cn(x).close 'comment this line out to recreate the problem       Set cn(x) = Nothing    Next    %>    </BODY>    <HTML>					
  2. In SQL Server, open the Performance Monitor: From the Start menu, select Programs, click Administrative Tools (Common) and then select Performance Monitor.
  3. Click + to add a counter.
  4. Change the object to SQL Server.
  5. Select the User Connections counter and click Add.
  6. Click Done.
  7. Run the ASP page.
  8. Watch the User Connections counter. If the connection is being returned to the pool then it will be reused and you only see the counter go up one through two connections from the starting point. If the connections begin to climb, then they are not being returned to the connection pool to be reused, and new connections are being created for each ADO object.

Conclusion

It is generally not a good practice to implicitly create connections. Whena connection is implicitly created you do not have a handle to theconnection to call and close the connection. The connection continues toremain open and unused until the connection times out. The default timeoutwith connection pooling enabled for SQL Server is 60 seconds. Whenconnection pooling is not on, SQL Server drops the connection immediatelyafter the ASP page has been parsed but there are other performance issuesto consider when connection pooling is not on.
REFERENCES
For additional information about connection pooling, please see the following article(s) in the Microsoft Knowledge Base:
169470 INF: Frequently Asked Questions About ODBC Connection Pooling
For additional information about connectionADO/ASP Scalability, please see the following article(s) in the Microsoft Knowledge Base:
176056 INFO: ADO/ASP Scalability FAQ
  1. Connect to Support Online at the following Internet address:
  2. In the My Question Is About box, select All Products.
  3. In the My Question Is box, type the Article ID (qnumber) of the article that you want to see. For example, type ""Q162192"" (without the quotation marks).
  4. Click Find (next to the My Question Is box). This search will return from one to several article titles. The Article ID appears in the upper-left corner of the article. You can see the Article ID only after you click to view the article.
  5. Click the title of the article to view it.
Properties

Article ID: 191572 - Last Review: 06/29/2007 03:20:06 - Revision: 2.3

Microsoft ActiveX Data Objects 1.5, Microsoft ActiveX Data Objects 2.0, Microsoft Active Server Pages 4.0, Microsoft Data Access Components 2.5

  • kbdatabase kbinfo KB191572
Feedback
dy>.com/c.gif?DI=4050&did=1&t=">