[Zope] SLQ-connection Pooling

[kapil thangavelu]

On Thursday 08 August 2002 02:10 am, Dario Lopez-K=E4sten wrote:
> Hello!
>
> I just realised I have never understood wether zope pools sql-connectio=
ns
> or not.

it does, even better, its in effect an adaptive pool. on a heavily hit si=
te=20
the number of connections will likely equal the number of zope threads,=20
connections are associated with volatile attributes of objects, as long a=
s=20
the objects remain in cache, the connections are maintained.

> I am using DCO2 and we are using PythonScripts and External
> methods to execute the queries - zsql-methods use too much memory (that=
 is
> sometimes not released) and are too slow for what we need to do, aside
> from the fact that we cannot use bind-vars in zsql-methods (yet I hope
> :-).

out of curosity, what version of DCOracle2 are you using? matt's fixed a=20
couple of things in cvs.

why do you find that zsql methods use too much memory? are you trying to=20
scroll large result sets?

the lack of bind vars is problematic, zope's sqlvars provide poor=20
alternatives.

but in the mechanism you described it sounds like your opening and closin=
g=20
connections on a regular basis, and possibly loosing transaction integrat=
ion=20
with zope :-(

if you really need direct access to an oracle connection, create a produc=
t=20
whose init opens a few connections, and store it in something like=20
http://www.zope.org/Members/k_vertigo/Products/ThreadSpecificStorage
and register the connection with the zope transaction on use via a proxy=20
(shared/dc/zrdb/tm.py is a good example), if you want transaction integri=
ty.

although, supposedly DCOracle2 is thread safe enough that it can handle=20
multiple thread access safely, so if you don't want transaction integrati=
on=20
(umm.. questionable at best, imo, but i demonstrate it because its easy ;=
-).=20
you can just open up a single connection in a module and leave it as a mo=
dule=20
attribute.

then from your external methods you can return the connection thusly,

from Products.OracleConnections import conn
def foo(bar):
   cursor =3D conn.cursor()
   # do some stuff

> What I am wondering about is connection pooling. I am not sure if a
> DB-connection object pools it's connections or not. This has some impac=
t
> on how we configure our oracle client.

the db adapter itself knows nothing of pooling (its an automatic benefit =
to=20
the way the zodb works),  the oracle connection is persistent as long as =
the=20
db adapter remains in the cache.

> Anyone has some insight to share on this?

hth,

kapil