the database connection pooling

Just to summarize and add a few words to This discussion (a link to the thread at The Design of Software)


Whenever one implements a 3-tier application, the database connection pooling must be used.
Exceptions:

1. light-weight applications
2. application with security policy implemented at the database side: if each application user has to log in to the database under his database account a) so the security restrictions of data access can be applied b) to audit his actions
3. database security capabilities are used to identify and authorise user

Database connection pooling is supported by most database connectivity API

It is supported by

1. JDBC starting from JDBC 2.0 (see a tutorial on JDBC connection pooling by Sun or an Oracle Magazine article ) so Java program can use it
2. ODBC starting from ODBC 3.0 (see Microsoft's FAQ about connection pooling for ODBC), so C++, MS Visual Basic etc application can use it
3. proprietary database connection APIs like Oracle Call Interface (OCI) see OCI Programmer Guide, Ch. 9 C++, C
   

Reasons:

1. the creation of a database connection takes some time (not short, up to 1 second to Oracle DBMS), so if users frequently connect/disconnect to the database, then connection pooling must be used. A good example would be an internet shop, such that many users connected annd disconnect it . From my experience I would 10 connections per second is a limit for non-pooled database access.
2. each database connection gets some memory at both client and server side to handle it. Even worse, if you run your Oracle in dedicated server mode, then each client has its own server process. It is not good at all if you do not run long-running queries, a batch job or some administrative task like using RMAN to restore/back up a database.
   
3. so, performance certanly degradated if you use one connection per user see some experiments JDBC Connection Pooling Best Practises
4. database vendor may charge you per connection or the number of connections may be restricted by some DBA policy .
   

Problems:

1. you have to implement proper multi-threading at the 2nd layer, see examples at the tutorial above how to do it
   
2. some database engines or server configurations may restrict the number of database locks per connection
3. performance may decrease if there are too many users so there are no enough connection in the pool to handle them. Just increase the number of connections. When the number of user decrease, decrease the number of connections. If your application is dynamic ... like internet-shop and the number of users very singificantly in time then creation and deletion of connection in the pool may degrate performance. you need some techniques based on queuu theory to forecast the number of connections.