JP2006511876A5

JP2006511876A5 -

Info

Publication number: JP2006511876A5
Application number: JP2004564792A
Authority: JP
Filing date: 2003-10-14
Publication date: 2008-12-18
Anticipated expiration: 2023-10-14

Claims

A method (400) for a local server to respond to a database query directed to at least one remote server, comprising:
Dynamically cache the results of previous database queries of the remote server (414);
Associating a local database with the local server (420);
Storing a plurality of cache results in a shared table in the local database (422);
Using the plurality of cache results in responding (412) to a new database query by the local server ;
The storing step comprises:
Insert the results of a simple query (304) and top N queries (305) on a single database table into a shared base table;
Store the results of the join query in a shared join table;
Forming a shared table repository including all the shared base tables and shared join tables;
Store the results of the combined query in a separate table, one for each query result,
Forming an exact match repository from the plurality of separate tables;
A method comprising stages.

The inserting step comprises:
Each time a simple query is first received in the base table, create a single local table in the cache to correspond to the base table in the origin server,
Inserting the result of the simple query into the local table;
Insert all the results of the next simple query into the base table into the local table;
The method of claim 1, comprising steps.

The storing step comprises:
Create a local join table in the cache to correspond to each join condition and table list in the join query;
Inserting all results of the next join query on the same table list and having the same join condition into the local join table;
The method of claim 1, comprising steps.

The step of trying is
A list of columns in the selection list of the new query is included in the matching query, and the predicate of the new query logically includes that of the matching query predicate and is described in various sections of the new query. Find a cached matching query that satisfies the condition that all columns are included in the selection list of the matching query;
When the list of columns in the new query selection list is included in each one of the matching queries and all predicates are connected by a logical OR operator, the new query predicates are: Logically includes those of predicates formed by all separation of the matching query predicates, and all columns described in the various sections of the new query are included in the select list of each of the matching queries Find a set of cached matching queries that satisfy
The method of claim 2 comprising at least one of the steps.

A method (400) for a local server to respond to a database query directed to at least one remote server, comprising:
Dynamically cache the results of previous database queries of the remote server (414);
Associating a local database with the local server (420);
Storing a plurality of cache results in a shared table in the local database (422);
Using the plurality of cache results in responding (412) to a new database query by the local server;
The step of using comprises
If the query is complex, search the exact match repository for responses to the new query,
Subselecting a response to the new query from the data in a shared table repository if the new query is not complex;
Including at least one of the stages;
The searching step includes
If the SQL string of the new query is found in the exact match repository, return the cached query result corresponding to the SQL string to the application;
If the SQL string of the new query is not found in the exact match repository, send the new query to the origin server;
If the SQL string of the new query is not found in the exact match repository, insert the received query result into the exact match repository;
Associating the query result in the cache with the SQL string of the new query if the SQL string of the new query is not found in the exact match repository;
A method comprising stages.

Cleaning the shared table
Forming a control column in each table from the shared table;
Set the entry in the control column to false;
Identify good queries that will be maintained in the shared table;
Mark all entries in the control column that correspond to rows belonging to responses for the good query,
Delete any row in the shared table whose entry in the control column is still false;
6. A method according to claim 5, comprising steps.

A method (400) for a local server to respond to a database query directed to at least one remote server, comprising:
Dynamically cache the results of previous database queries of the remote server (414);
Associating a local database with the local server (420);
Storing a plurality of cache results in a shared table in the local database (422);
Using the plurality of cache results in responding (412) to a new database query by the local server;
The step of using comprises
If the query is complex, search the exact match repository for responses to the new query,
Subselecting a response to the new query from the data in a shared table repository if the new query is not complex;
Including at least one of the stages;
The sub-selecting step comprises:
Attempting to prove that the result of the new query result set is contained within a cached query result union having an containment checker;
If the proof-attempting step is successfully completed, replace the table described in the new query with a local table name;
Evaluate the new query on the shared table repository when the proof-attempting step is successfully completed;
If the query is the top N queries, count the number of rows received from the local cache and rewrite the new query if the number of rows does not exceed the number of rows required by the application Create a rewritten query, send the rewritten query to an origin, store the results received from the origin server in a shared table repository, and write the rewritten query predicate to the Append to the index of the shared table repository, evaluate the query on the shared table repository, and return the result of the evaluation stage to the application;
A method comprising stages.

Said rewriting step comprises:
Append the primary key of the accessed table to the selection list of the query;
If the resource manager assumes that the resulting set of results is not too large or worth caching, it removes the aggregation operator from the selection list,
Expand the number of rows required by the query (N) and retrieve more rows;
8. The method of claim 7, comprising the steps.

A method (400) for a local server to respond to a database query directed to at least one remote server, comprising:
Dynamically cache the results of previous database queries of the remote server (414);
Associating a local database with the local server (420);
Storing a plurality of cache results in a shared table in the local database (422);
Using the plurality of cache results in responding (412) to a new database query by the local server;
Further comprising ensuring data consistency of previous query results,
Subscribe the data cache to refresh messages communicated by the origin and applied to backend tables, including updates, deletes, and inserts;
Each time the refresh message is received, apply the updates, deletes, and inserts to locally cached tables in a shared table repository;
Invalidate any shared join table in the exact match repository referenced in the refresh message as modified,
Associating a timestamp of the refresh message with a refresh message, the effect of which is equal to the maximum timestamp of any transaction contained within the message;
Associating a cached set of timestamps with a previous query result equal to the timestamp of the last refresh message applied to the cache;
Associate a new query timestamp with the new query,
Comparing the timestamp of the new query with the cached set of timestamps;
Inserting a response into the new query if the new query timestamp exceeds the cached set of timestamps;
Retrieve a pending refresh message from the database server if the timestamp of the new query exceeds the cached set of timestamps;
A method comprising stages.

A method (800) for ensuring data consistency of a set of query results retrieved from a database server, comprising:
Subscribe the data cache to a refresh message that is communicated by the origin and applied to the backend table (707), including updates, deletes and inserts;
Each time the refresh message is received, apply the updates, deletes and inserts to a locally cached table (704);
Associating a time stamp with a refresh message whose effect is equal to the maximum time stamp of any transaction contained within the message;
Associating a set of timestamps with a previous query result equal to the timestamp of the last refresh message applied to the cache;
Associate a new query timestamp with the new query,
Comparing the timestamp of the new query with the set of timestamps;
Inserting a response to the new query if the timestamp of the new query exceeds the set of timestamps;
Retrieving a pending refresh message from the database server if the timestamp of the new query exceeds the set of timestamps;
A method comprising steps.

A system including an edge server (109) for dynamically caching data based on a query executed by a local application and ensuring data consistency of a set of query results retrieved from a database server (100),
Means for dynamically caching the results of a remote database's previous database query;
Means for associating a local database with a local server;
Means for storing a plurality of cache results in a shared table in the local database;
Means for using the plurality of cache results in responding to a new database query by the local server;
Means for subscribing the data cache to refresh messages communicated by the origin and applied to backend tables, including updates, deletes and inserts;
Means for applying the update, delete, and insert locally to the cached table each time the refresh message is received;
Means for associating a time stamp with a refresh message whose result is equal to the largest time stamp of any of the transactions contained within the message;
Means for associating a set of timestamps with a previous query result equal to the timestamp of the last refresh message applied to the cache;
Means for associating a new query timestamp with the new query;
Means for comparing the timestamp of the new query with the set of timestamps;
Means for inserting a response to the new query if the timestamp of the new query exceeds the set of timestamps;
Means for retrieving a pending refresh message from the database server if the timestamp of the new query exceeds the set of timestamps;
Including system.

A program for dynamically caching data based on a query executed by a local application and ensuring data consistency of a set of query results retrieved from a database server, the program comprising: In addition,
Dynamically cache the results of the remote server's previous database query (414);
Associating a local database with the local server (420);
Storing a plurality of cache results in a shared table in the local database (422);
Using the plurality of cache results in responding (412) to a new database query by the local server;
Function and
Subscribe the data cache to refresh messages communicated by the origin and applied to backend tables, including updates, deletes, and inserts;
Each time the refresh message is received, apply the updates, deletes, and inserts to a locally cached table;
Associating a timestamp with a refresh message whose effect is equal to the maximum timestamp of any transaction contained within the message;
Associating a set of timestamps with the result of a previous query equal to the timestamp of the last refresh message applied to the cache;
Associate a new query timestamp with the new query,
Comparing the timestamp of the new query with the set of timestamps;
Inserting a response to the new query if the timestamp of the new query exceeds the set of timestamps;
Retrieving a pending refresh message from the database server if the timestamp of the new query exceeds the set of timestamps;
Function,
The program to be executed.