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

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 

Continues…

Pages: 1 2 3




Related Articles :

  • No Related Articles Found

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

  1. First and foremost, I am thrilled to see that someone took the time to actually test this. It’s been on my mind as I’ve noticed variations in several very high traffic sites I run. You took the time to not only write this up with images, but you graphed the results. Thank you!

    Here are my findings, there is much more to this then one might think.

    - SQL Server itself has keep alives in connection configuration. It also has a keep alive ping to check. This has to be high enough so that the keep alives are not expiring before the connection can be reused depends on your web app

    - IIS, according to Microsoft and now I cannot find the MSDN link, keep alives have to be enabled there too. Make sure not too high or you will have a lot of HTTP TIME_WAIT on your stateful firewall and server.

    - Best driver.. You have a few options, you can use SQLOLEDB as a provider or the native client which is SQLNCLI10 or SQLNCLI if not SQL Server 2008. OR, you can use ODBC to handle SQL, depending on your Windows version ODBC has a connection pooling tab that lets you define the number.

    I found using SQLOLEDB or native client to be much faster than ODBC, by many times too.

    If your using classic ASP it’s a bit tough if your site constantly requires database connections and using global#asa for a session based db open is a bad idea.

    ASP.NET on the other hand allows better performance. Also found setting the SQL Packet size in the connection string to have a big impact. 4096 is the default max network packet, but if your returning or using less than 4096 bytes in your query (including recordset) then lower it in your connection string, NOT THE SQL SERVER. To due this just append “yourstring…;Packet Size=512;” <- added Packet Size=X; If it needs more, it will just send multiple packets but it will reduce network I/O significantly. If your queries are huge (which shouldn't be, use stored procedures and try to avoid cursors) Remember the most common MTU is 1500. You could also do 1024. Default when unspecified is 4096.

    - If your SQL Server is on the same machine as IIS forget TCP, use shared memory. See here: "Provider=SQLNCLI10;Data Source=lpc:(local);…rest of string" lpc:(local) implies its local use memory.

    Many might say well does this all really matter. If you have minimal or little traffic and don't mind resetting IIS every so often then no but running a high traffic site such as one of ours its tremendous. Reliability wise and resource wise. Code should be clean and efficient, the author of this page did a good job of detailing the differences.

    I will attempt to run some tests too, but I ended up writing my own .NET lib for pooling. It runs as a Windows Service and then you use the query with the lib instead of Microsoft ADO. It's a way of semi transforming a old (but huge) ASP site in to .NET in some aspects such as this.

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |