[DB-SIG] client side sub queries

[Chris Clark]

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 
>>> 2nd.
>>>
>>> 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
>> together.
>>
>> 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 
server 3.

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).

Chris