Connetion pooling and statement caching: pros and cons

From: ?skar Sturluson (skari_at_tm.is)
Date: 10/01/03 

Date: 1 Oct 2003 09:04:15 -0700

Hello all,

I am wondering if someone here knows of a good overview (preferably
http accessible) of the pros and cons of Connection pooling and
statement caching.

Also: does anyone know if it is possible to use a plain connection
object (i.e. not pooled) but still give seperate threads their own
transactions? My research here indicates this is not possible, but it
doesn't hurt to ask.

A litle background on why I am asking:

I am developing a web application using Oracle 9i. I have made two
test applications, one using pooling with stament caching and the
other using a plain connection.

What I like about the pooling solution is that each user gets a
virtual connection with its seperate transaction control.
What I dislike is that when a users connection is closed all
statements in the statement cache are cleared. This is akward since
it means that statements have to re-prepared for each user, thus
greatly dimishing the value of the statement cache.

The plain connection solution has the drawback that having seperate
transactions for different clients seems impossible. Of course I also
need to implement my own statement caching, but that is straight
forward: just use HashMaps to store prepared statments. But when it
comes to transactions I am at a loss. Commiting attempts to commit
for all clients/threads, since there is just one user logged on
through the connection.

On the whole the plain connection implementation appears to be
considerable faster (i.e. for lookup and querying), although I haven't
done any real "scientific" measurements on that.

thanks,
Oskar

Relevant Pages

  • Re: ODBC/OLE DB Connection Pool
    ... > Connection Pool (I have already completed the TCP/IP tasks without ... turning off pooling is the errors you are seeing. ... problems are occurring with SQL Server; for example, ... ASP developers should open one connection per set of unique user ...
    (microsoft.public.data.ado)
  • Re: ODBC/OLE DB Connection Pool
    ... > Connection Pool (I have already completed the TCP/IP tasks without ... turning off pooling is the errors you are seeing. ... problems are occurring with SQL Server; for example, ... ASP developers should open one connection per set of unique user ...
    (microsoft.public.inetserver.asp.db)
  • Re: ODBC/OLE DB Connection Pool
    ... > Connection Pool (I have already completed the TCP/IP tasks without ... turning off pooling is the errors you are seeing. ... problems are occurring with SQL Server; for example, ... ASP developers should open one connection per set of unique user ...
    (microsoft.public.data.oledb)
  • Re: Question about IDispose
    ... > SqlConnection.Close() if the connection is not already closed. ... > handles pooling just fine does). ... >to write a Dispose method that works properly with the pooling system ... >> are cheap, however, the reverse is true in a web environment. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: ODBC/OLE DB Connection Pool
    ... > And if there is no global ADODB.Connection, there will be no pooling. ... The ADO Connection object implicitly uses IDataInitialize. ...
    (microsoft.public.data.ado)