[Zope] SLQ-connection Pooling

Dario Lopez-Kästen dario@ita.chalmers.se
Thu, 8 Aug 2002 12:35:53 +0200


From: "Bo M. Maryniuck" <b.maryniuk@forbis.lt>
> On Thursday 08 August 2002 11:10, Dario Lopez-Kästen wrote:
> > or not. 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
>
> What is DCO2? Is it DCOracle2 for Oracle DB or something else?

Yes, it is DCOracle2, the oracle DB adapter for python/zope.

> If you use Oracle with DCOracle2, then yes, you can't use bindings and
can't
> user IN/OUT procedures in ZSQLMethod and you should do this in
ExternalMethod
> or (much better) in ZProduct.

yes, I know that. ZSQL methods can't be used for queries that want to use
bind vars. Soemthing that is not easily fixable unless one makes changes to
DTML, IIRC (it's in the archives somewhere).

I can't be bothered to write a proper product just yet. Besides, I want to
read up on the source of the DA, zSQL, RDB stuff in Zope first, and I just
don't have the time.

> But you still does not in the current
> transaction.
> ExternalMethod in this case is not so good solution because it
> is slower, difficulty transportable, has bad intergration to your project
etc
> (IMHO :).

well, we gave up on the zope transaction mechanism about 9 months ago
(despite my objections), and I haven't had the time to fix that yet. Of
course I am now seeing what I think are the effects of that (possibly silly)
decision, but that is the way it is.

Not sure what you mean here by not good solution here... The query is
prepared in a PythonScript. It is then sent by the PythonScript to an
External Method that connects as the given user, prepares the query,
executes it, collects the result disconnects and sends back a result set to
the PythonScript, which then hands it back to the rest of Zope.

All this *should* in theory work pretty well, and (I hope) still work within
the transaction.

The effects we are seing is that queries execute and return their result set
within 1-3 seconds instead of having to wait 30-130 seconds for the result
set.

This still doesn't answer the actual question. Are SQL connections pooled in
Zope or not, i.e. if I have a connection object that is open, does that mean
that that connection is pooled?

Thanks,

/dario