Fog Creek Software
Discussion Board

Welcome! and rules

Joel on Software

database connection pool


I am new to ASP.NET. I am designing a web site for students and it will be getting lot of traffic.  In environment like this what is the best way to handle database connections.  Many people told me I have to have connection pool to handle many connections.  I know some database comes with it owns connection pool.  Should I use connection pool that comes with database or should I implement my own?  I would like to get your opinion.  If know any place to I can get more information, how to design connection pool, please post the url.


Wednesday, October 27, 2004

ASP.NET on Windows server 2003 pools connections by default (I think W2K does as well but i haven't used it for more then a year or so) and does fairly well by MS SQL Server - you can twiddle with settings in the machine.config but make sure you look at your db vendors best practices before you really get deep in.  More important from a performance perspective is to make absolutely certain that you execute no sql that doesn't have a precompiled execution plan.  This means you use stored procedures for everything or make deadly certain that you NEVER EVER concatenate in a value when building a sql statement - always use bind variables.  If you do not your db will eventually wet the bed when tryting to optimise too great a number of sql statements over a short period of time.  Further you will find that you are continously flushing your stored sql cache.

If you can push all state off of the server and into cookies or in form variables you will buy huge perf there as well.

Wednesday, October 27, 2004

All MS-provided database libraries do connection pooling by default when the ConnectionStrings are exactly equal.

While this gives you a significant performance boost for free, it also means you must be sure to close all your database connections immediately when you're done with them.

What that means

1) ALWAYS ALWAYS ALWAYS close the connection in the same function where you open it.

2) ALWAYS ALWAYS ALWAYS close your connection in a Finally block (in C#, you can use the "using" statement instead).

If you don't do #2, then you'll leak a connection every time there is any exception in your DB code.

Richard P
Wednesday, October 27, 2004

*  Recent Topics

*  Fog Creek Home