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

  • 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 

    Continues…

    Leave a Reply

    Your email address will not be published. Required fields are marked *