* See MSDN for this information. The default settings are usually configured for pooling. To actually prove out the correct method and to demonstrate that my connections were even being pooled correctly, I tested various connection pooling implementations and used a deliberate non-implementation (unique userid/pwd) as the control group. My groups were:
- Not explicitly closing or destroying connections
- Closing but not destroying connections
- Closing AND destroying connections
I ran 3* loops of creating connections in a connection array and monitored the results using NT Performance Monitor (connections) and SQL Profiler (sessions). I also timed the trials to see the performance impact for VB6 only. For each time trial I ran loops of 100 in each case. *for ease of illustration
If Connections are Not Closed or Set to Nothing (Connection Creep):
Private Sub Test()
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
Next
End SubPerformance Monitor Results: Three connections resulted from running the above code in both VB and ASP.
SQL Server Profiler Trace Results: Three connections are made. They aren’t disconnected until the application is closed.
Connect
ExistingConnection
Connect
ExistingConnection
Connect
ExistingConnection
Disconnect
Disconnect
Disconnect
Total Time: 6.36 seconds
If Connections are Closed Only (Connection Pooling Implemented
Private Sub Test()
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
Next
End