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

End SubPerformance Monitor Results: Only one new connection is made – it doesn’t appear that pooling is working


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.

SQL Server Profiler Trace: Four connections and disconnections are made, which is exactly like the example where pooling was not implemented properly and each new connection had to be created anew.

ASP Trace Results


Total Time: 7.66 Seconds* 

*This performance number was extrapolated because I didn’t have 100 unique user connections. I used the average of the 2nd two connections (not including the first) and multiplied by 50.


The performance results were significant in that the pooled connections were considerably faster. This stands to reason as connections are being retrieved from a pool and not created from scratch like in the other two cases where the object is either destroyed and not released into the pool, or held onto and not released into the pool. Not closing connections results in unneeded connections to “hang,” causing “connection creep”. Be sure to explicitly close all of your connections when you are done with them.

Other Useful References


The SQL Server Profile result set was obtained using a VB test driver and setting “Connections” to “Nothing” after every iteration.Here’s a screen shot of SQL Server Profiler using the following code:

The code to run “as is” in either VB or ASP environments:Dim cn(20)
Dim rs
Dim n

For n = 0 To 20
  Set rs = CreateObject(“ADODB.Recordset”)
  Set cn(n) = CreateObject(“ADODB.Connection”)


  rs.Open “select * from authors”, cn(n)

  Set rs = Nothing
  Set cn(n) = Nothing

Pages: 1 2 3


No comments yet... Be the first to leave a reply!