articles >> performance tuning >> Use the Correct ASP (ADO) Syntax to ...

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

By : Brian Lockwood Jun 10, 2003

There is much confusion about how to actually implement connection pooling in VB/VBScript code. In this article I have actually tested various ASP syntax variations and verified the results when using connection pooling.

To preface - here are some highlights and some recent findings:

• Setting connections to "Nothing" in ASP does NOT affect connection pooling - only closing or not closing the connections will affect it if you are using the same connection string. It is recommended with the current version of ADO 2.1 to set connections to "Nothing" - which is consistent with current Microsoft articles.
• Although your ODBC driver, the Registry, and SQL Server can all be configured properly,* and you are using identical connection strings for all of your connections, you STILL may not be getting the benefit of connection pooling.

* 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:

1. Not explicitly closing or destroying connections
2. Closing but not destroying connections
3. 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