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

Connect 
ExistingConnection 
Disconnect 
Connect 
ExistingConnection 
Disconnect 
Connect 
ExistingConnection 
Disconnect 

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.

Conclusions

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

http://support.microsoft.com/support/kb/articles/Q191/5/72.ASPhttp://msdn.microsoft.com/library/techart/pooling2.htm

Appendix

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”)

  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

Pages: 1 2 3




Related Articles :

  • No Related Articles Found

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

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 |