Re: Design problem About application related with cached rows
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 [EMAIL PROTECTED]: 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/[EMAIL PROTECTED] -- Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Design problem About application related with cached rows
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 [EMAIL PROTECTED]: 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/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Design problem About application related with cached rows
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 [EMAIL PROTECTED]: 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 [EMAIL PROTECTED]: 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/[EMAIL PROTECTED] -- Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
