[Zope-DB] SQL Relay and _v_ variables

[Dieter Maurer]

Chris Withers wrote at 2003-10-23 18:43 +0100:
 > Andreas Kaiser wrote:
 > > It comes with it´s own DA: ZSQLRelayDA.
 > 
 > Interesting to note that this DA doesn't use _v_ variables.
 > 
 > It opens a connection at the beginning of the transaction and closes when the 
 > transaction is finalised or aborted.

That's because often opening a database connection is very expensive (rumours
say, this is true for Oracle). The SQLRelay author knows that
this is not the case for the connection to the SQLRelay proxy and
therefore opens and closes frequently.

However, the DA must be very careful to not open a second connection
to the same database within the same request. Otherwise, what
should be one transaction may become several independent ones.
This can lead to various obscure failures (e.g. database inconsistencies).

Therefore, I expect that the connection must be stored by the DA,
at least for the duration of the request. "_v_" attributes are
quite convenient for this.


 > This mirrors the pathalogical ZOracleDA case, except that it doesn't leak 
 > connections as far as anyone has commented ;-)

"ZOracleDA" does *not* open/close the connection per request...

Thus, what is mirrowed?

 > I'm guessing the constant opening/closing of connections is slower than keeping 
 > them around, but maybe not as slow as it would be, since by the looks of it, SQL 
 > Relay will live on the web server, and should be designed to be quick and 
 > lightweight to connect to.
 > 
 > Can anyone confirm this?

Yes.

 > Who's using SQL Relay under high load with lots of database adapters in production?

We used it to connect from Linux/Solaris to an MS-SQL Server on Windows.
It became too slow when large amounts of data had to be transfered
from the database to the client. In this case,
"SQLRelay --> FreeTDS --> SQL-Server" was an order
of magnitude slower than "Sybase-DB-Api --> FreeTDS --> SQL-Server".

Thus, the problem was not the connection time but the handling
of large data volumes.

-- 
Dieter