SubPerformance Monitor Results: A maximum of two connections are made, and the rest appear to be pooled. The connections are destroyed when the application is closed in both VB and ASP.
SQL Server Profiler Trace Results: Only two connections are made, they aren’t destroyed until the application is closed, or the connections time out after 60 seconds.
Connect
ExistingConnection
Connect
ExistingConnection
Disconnect
Disconnect
Total Time: 2.56 Seconds
If Connections are Closed and Set to Nothin
Private Sub Form_Load()
Dim rs
Dim n
For n = 0 To 2
Set rs = CreateObject(“ADODB.Recordset”)
Set cn(n) = CreateObject(“ADODB.Connection”)
cn(n).Open “DRIVER={SQL Server};SERVER=SQLSERVER;DATABASE=Pubs;UID=sa”
rs.Open “select * from authors”, cn(n)
rs.Close
Set rs = Nothing
cn(n).Close
Set cn(n) = Nothing
NextEnd
SubPerformance Monitor Results: Only one new connection is made – it doesn’t appear that pooling is working for VB, but Performance Monitor shows proper pooling behavior for ASP.
SQL Server Profiler Trace Results: For VB, three connections and disconnections are made, unlike in the example where pooling was working in which starting with the 3rd connection no more new connections were created. For ASP, on the other hand, destroying the connections has no effect and pooling is still in effect.
Note: this blip was induced by a slight delay so Performance Monitor could pick up the connection. Normally, no change is seen in Performance Monitor at all.
ASP Trace Results
Connect
ExistingConnection
Connect
ExistingConnection
[No more trace activity even though a 3rd connection is created!]
(after 60 seconds)
Disconnect
Disconnect
Total Time: 2.62 seconds
If No Pooling is Attempted (Unique UID/PWD
Private Sub Form_Load()
Dim rs
Dim n
Dim str
For n = 0 To 2
Set rs = CreateObject(“ADODB.Recordset”)
Set cn(n) = CreateObject(“ADODB.Connection”)
Select Case n
Case Is = 0
str = “DRIVER={SQL Server};SERVER=SQLSERVER;DATABASE=Pubs;UID=test1”
cn(n).Open str
Case Is = 1
str = “DRIVER={SQL Server};SERVER=SQLSERVER;DATABASE=Pubs;UID=test2”
cn(n).Open str
Case Is = 2
str = “DRIVER={SQL Server};SERVER=SQLSERVER;DATABASE=Pubs;UID=test3”
cn(n).Open str
End Select
rs.Open “select * from authors”, cn(n)
rs.Close
Set rs = Nothing
cn(n).Close
Set cn(n) = Nothing
Next n