[DB-SIG] client side sub queries
Chris.Clark at ingres.com
Wed May 23 20:37:46 CEST 2007
Carl Karsten wrote:
> Carsten Haese wrote:
>> On Tue, 22 May 2007 17:33:27 -0500, Carl Karsten wrote
>>> Or some such abomination of results of one query as a parameter of a
>>> given my use case, I can understand why this isn't in the spec, and
>>> why it may never be. but it seems to come up more often that I
>>> would expect, so here we go.
>>> My current problem: reconcile transaction details that are off due
>>> to rounding errors. the 2 sets of details are stored on different
>>> servers, and no chance of getting one server to hit the 2nd, so the
>>> python client code is going to have to help by getting a list of
>>> keys from one and constructing "WHERE OtherKey IN ( 'key1', 'key2',
>>> 'key3', ...)" which isn't 'hard' but I find annoying that I have
>>> to convert formats in the application layer.
>> Option 1: Create a temporary table on one server and load the data from the
>> other server into it. Then, use a server side subquery or join the tables
>> If you don't have write permission on either server, there is Option 2: Fetch
>> all relevant data from server 1 into client memory, fetch all relevant data
>> from server 2 into client memory, and do the reconciliation in client memory.
> The perms issue can be taken care of by having the table created ahead of time.
> but, the application level code is still 'custom' and falls into a similar pit
> as embedding parameters into the SQL command string.
From my ivory tower, this sounds more like a database problem than an
application problem. I agree completely that having to implement this
logic in the application is awkward. Is replication an option with the
database you are using? Either replicating from server 1 into server 2
(possibly vice-versa) or replicate from server 1 into server 3 and
replicate from server 2 into server 3 so you can perform all queries in
With replication you tend to have options of either batch extract/load
(ETL) or real time replication (usually implemented by the database
itself, sometimes by a 3rd party tool). The downside of replication are
increased space requirements and possibly delays in replication if it is
not real time (for a reporting situation like this, collisions should
not be an issue).
More information about the DB-SIG