Design problem About application related with cached rows

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Design problem About application related with cached rows

Kostas Karadamoglou
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, ...);



-------------------------------------------------------
SF.Net email is Sponsored by the Better Software Conference & EXPO
September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Design problem About application related with cached rows

Fred Toussi-2

----- Original Message -----
From: "Kostas Karadamoglou" <[hidden email]>
To: <[hidden email]>
Sent: 14 August 2005 10:37
Subject: [Hsqldb-user] Design problem About application related with cached
rows


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



-------------------------------------------------------
SF.Net email is Sponsored by the Better Software Conference & EXPO
September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user 



-------------------------------------------------------
SF.Net email is Sponsored by the Better Software Conference & EXPO
September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Design problem About application related with cached rows

Fred Toussi
In reply to this post by Kostas Karadamoglou
This sort of replication is almost always done using timestamps in the rows
of the main database. Queries will have a WHERE clause that accepts only
those rows that were added or modified after the last synchronization. The
remaining issue is the rows that are deleted from the main database. This is
usaully handled with a trigger in the main database that inserts the deleted
rows (or just the primary keys for the rows) into special tables. These
tables are then used when synchronizing to decide which rows of the cache
database to delete.

In short, it is generally not practical to perform synchronization without
the extra timestamp data. Without this data, it is more efficient to drop
the whole cache database and make a new copy of the main database.

Fred
----- Original Message -----
From: "Kostas Karadamoglou" <[hidden email]>
To: <[hidden email]>
Sent: 14 August 2005 10:37
Subject: [Hsqldb-user] Design problem About application related with cached
rows


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



-------------------------------------------------------
SF.Net email is Sponsored by the Better Software Conference & EXPO
September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user 



-------------------------------------------------------
SF.Net email is Sponsored by the Better Software Conference & EXPO
September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Loading...