List:General Discussion« Previous MessageNext Message »
From:Pooly Date:August 14 2005 3:27pm
Subject:Re: Design problem About application related with cached rows
View as plain text  
Always reply to the lists, others may have better ideas, others insights...
I may have not understand everything in your previous description. Why
would you need an extra software on the remote DB ??
All the steps I've describe should work from the client.

All you want is a kind of cache for the client, that you can
invalidate when you judge it needed ?

If you need to check UPDATE on your remote DB, you're stuck, except
with 5.0.10 (at least) with triggers. If it's only INSERT, then you
just have to query the last rows you don't have (with a timestamp, an
ID).
If you don't have any total order relationship in your data,but have a
unique ID, retrieve all the ID from your remote DB, remote with the
one you have locally, and then query the full rows. No extra software
needed on the server !

2005/8/14, Kostas Karadamoglou <karadamoglou_k@stripped>:
> I thought this solution but it needs an extra software at the remote
> database. I would prefer to contact from the cache database directly to
> the remote database using SQL.
> 
> But even using those 3 steps there is a problem. The only interface that
> I have is JDBC/SQL. That means that the only solution would be to fetch
> all the rows in a resultset locally(remote database) and then in a for
> loop I must check all the IDs that I dont need with the ID of the
> current row within the loop.
> 
> I think this is a little bit time consuming!!
> 
> Do you know I more effective way to do this?
> 
> thank you in advance, Kostas
> 
> Pooly wrote:
> > Hi,
> >
> > Try in in two fold.
> > 1. Get all the ID you have in your remote DB
> > 2. check those localy with the ones you have in cache
> > 3. Get all the info you need after you remove the ID you already have.
> >
> > 2005/8/14, Kostas Karadamoglou <karadamoglou_k@stripped>:
> >
> >>Hello,
> >>
> >>I try to create an application for my dissertation that caches rows from
> >>an romote database. The application handles a cache database which is
> >>identical with the original. The only difference is that it does not
> >>have autogenerated fields and referential integrity is omitted.
> >>
> >>I have designed a caching algorithm specific to the context of my
> >>applocation. However, I have a problem that I cannot solve it:
> >>
> >>I want to send a query to the remote database and then store the result
> >>to the cache instance. The cache database might have rows that can be
> >>duplicate with some rows of the resultset retrieved from the query.
> >>
> >>The easy solution is to insert all the rows of the resultset one by one
> >>after I check their existence at the cache table. However, this solution
> >>impose network latency to the network because useless data is moved on
> >>the net.
> >>
> >>Do you know any efficient way to fetch the exception (the rows that dont
> >>exist at the cache instance) of rows from the remote database using sql
> >>queries?
> >>
> >>I tried to use the following kind of query but the database returns an
> >>overflow message if the query string is too long.
> >>
> >>SELECT * FROM Customers WHERE CustomerID NOT IN (01,02, 03, ...);
> >>
> >>
> >>--
> >>MySQL General Mailing List
> >>For list archives: http://lists.mysql.com/mysql
> >>To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> >>
> >>
> >
> >
> >
> 


-- 
Webzine Rock : http://www.w-fenec.org/
Thread
Design problem About application related with cached rowsKostas Karadamoglou14 Aug
  • Re: Design problem About application related with cached rowsPooly14 Aug
    • Re: Design problem About application related with cached rowsKostas Karadamoglou14 Aug
      • Re: Design problem About application related with cached rowsPooly14 Aug