[egenix-users] Pooling connections
jan.murre at catalyz.nl
Tue Nov 14 21:24:34 CET 2017
On Sun, Nov 12, 2017 at 4:47 PM, M.-A. Lemburg <mal at egenix.com> wrote:
Thanks for your thourough answer!
On 09.11.2017 21:02, Jan Murre wrote:
> > Hi,
> > We are using mxODBC with the native Microsoft MS SQL Linux driver
> > 13.0) and we are opening and closing a connection for every database
> > This is considered to be not very efficient, although I am not sure if
> > mxODBC does some clever pooling of connections or not.
> > We tried to add pooling at the unixODBC level, according to this:
> > http://www.unixodbc.org/doc/conn_pool.html
> > [ODBC]
> > Pooling=Yes
> > And in the drivers section:
> > CPTimeout=120
> > After that, our connections fail to work with this error:
> > [unixODBC][Driver Manager]Driver does not support this function
> unixODBC connection pooling often creates issues with drivers.
> We recommend against using this.
> Yeah, we tried using the pooling feature, but it does not seem to work at
Just strange errors!
> > My question is, what is the best way to do connection pooling with mxODBC
> > and the native Microsoft MS SQL linux driver?
> The best way is to do connection pooling at the application
> level. You typically only need to open connections once
> per application (and reopen them in case they fail for some
> In the application itself, you then just run conn.commit()
> and conn.rollback() to delimit the transactions and then open
> cursors for running queries.
> The simplest way is to open a new cursor for every query,
> but it's, of course, also possible to use cursors for multiple
> You can also go one step further and cache cursors, i.e. always
> using the same cursor for the same common query, without closing
> them. You only have to make sure that you close the result sets
> when passing them back to the pool (using cursor.flush()).
> mxODBC it self does not provide connection pooling, since getting
> this right is difficult if you don't know what the application is
> doing (e.g. adjusting connection settings or keeping cursors alive).
> We do have connection pooling in mxODBC Zope DA and it's working well
> for Zope.
I tried this approach, it works OK.
The only thing is, that the overhead of opening and closing the connections
time is very small. I tried calling a particular stored proc 50 times in a
and without opening/closing the connection in between. Timings are:
Re-using the connection:
Opening and closing each time.
So, that is only ~ 0.4 ms overhead per call.
Maybe the native Microsoft Linux driver is doing some clever re-use of
> Best Regards,
> Marc-Andre Lemburg
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the egenix-users