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 the
following 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
- 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>
- In SQL Server, open the Performance Monitor: From the Start menu, select Programs, click Administrative Tools (Common) and then select Performance Monitor.
- Click + to add a counter.
- Change the object to SQL Server.
- Select the User Connections counter and click Add.
- Click Done.
- Run the ASP page.
- 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. When
a connection is implicitly created you do not have a handle to the
connection to call and close the connection. The connection continues to
remain open and unused until the connection times out. The default timeout
with connection pooling enabled for SQL Server is 60 seconds. When
connection pooling is not on, SQL Server drops the connection immediately
after the ASP page has been parsed but there are other performance issues
to consider when connection pooling is not on.