Use the Correct ASP (ADO) Syntax to Enable Connection Pooling for Best SQL Server Performance

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