Aggregate query-caching in databases architectures with a differential buffer and a main store

• US 9,740,741 B2
• Filed: 02/10/2014
• Issued: 08/22/2017
• Est. Priority Date: 04/11/2013
• Status: Active Grant

• Current Assignee: Hasso-Plattner-Institut für Softwaresystemtechnik GmbH
• Original Assignee: Hasso-Plattner-Institut für Softwaresystemtechnik GmbH
• Inventors: Plattner, Hasso, Mueller, Stephan, Krueger, Jens, Mueller, Juergen, Schwarz, Christian
• Primary Examiner(s): Dwivedi, Mahesh
• Assistant Examiner(s): Johnson, Johnese
• Application Number:
  US14/176,711
• Publication Number:
  US 20140310232A1
• Time in Patent Office: 1,289 Days
• Field of Search: 707602
• CPC Class Codes: G06F 16/24539 using cached or materialised query results
• CPC Class Codes: G06F 16/24552 Database cache management

Abstract

The invention relates to a computer system for both online transaction processing and online analytical processing, comprising: a processor coupled to a database, the database comprising the database comprising: a main store (116) for storing records, a differential buffer (114) for receiving and buffering added or deleted or modified records, the differential buffer being coupled to the main store, a schema comprising records stored in the main store and records stored in the differential buffer, and a cache store (112) for caching a result of a query against the schema; and a cache controller (106) executable by the processor and communicatively coupled to the database, the cache controller being configured for: storing the result of the query in the cache store; receiving an analytical request; and determining, in response to the received request, an up-to-date result of the query by (216): accessing the cache store to obtain the cached result; determining the records of the schema that have been added or deleted or modified since the step of storing the cached result in the cache store on the basis of the records stored in the differential buffer; and incrementally deriving the up-to-date result from the cached result and from the records determined in the previous step.

Claims

1. A computer system for both online transaction processing and online analytical processing, comprising:

a processor;

a database coupled to the processor, the database comprising;

a main store for storing records,

a differential buffer for receiving and buffering added or deleted or modified records, the differential buffer being coupled to the main store,

a schema comprising records stored in the main store and records stored in the differential buffer, and

a cache store for caching a result of a query against the schema, wherein the query involves calculating an aggregate function; and

a cache controller executable by the processor and communicatively coupled to the database, the cache controller being configured for;

storing a result of the query in the cache store as a materialized aggregate;

receiving an analytical request; and

determining, in response to the received request, an up-to-date materialized aggregate by;

accessing the cache store to obtain the cached materialized aggregate;

determining the records of the schema that have been added or deleted or modified since the step of storing the cached result in the cache store on the basis of the records stored in the differential buffer; and

incrementally deriving the up-to-date materialized aggregate from the cached materialized aggregate and from the records determined in the previous step,

wherein the cache controller is further configured for updating the cached materialized aggregate according to a merge update strategy, the merge update strategy comprising;

replacing the cached materialized aggregate in the cache store in response to the computer system initiating a merge of the records of the differential buffer into the main store, by;

accessing the cached query from the cache store during the merge;

determining the records of the schema that have been added or deleted or modified since the step of storing the cached result in the cache store on the basis of the records stored in the differential buffer;

incrementally deriving the up-to-date materialized aggregate from the cached materialized aggregate and from the records determined in the previous step; and

replacing the cached materialized aggregate in the cache store with the up-to-date materialized aggregate.

2. The computer system according to claim 1,

wherein the database further comprises a cache management store for storing an existence indicator indicating whether the cached result exists or not;

and

wherein the cache controller is configured for;

determining, in response to the received request, an up-to-date result of the query by;

checking the existence indicator as to whether there is a cached result of the query in the cache store, and

if there is a cached result of the query in the cache store, accessing the cache store to obtain the cached result as the up-to-date result of the query;

or

if there is no cached result of the query in the cache store, taking alternative action comprising running the query against the entire schema in order to obtain the up-to-date result.

3. The computer system according to claim 1,

wherein the step of incrementally deriving includes:

running the query against those records of the schema that have been added or deleted or modified between the step of storing the cached result in the cache store and the step of receiving the request; and

combining the results of the latter query with the accessed cached result in order to obtain the up-to-date result.

4. The computer system according to claim 1,

wherein the database further comprises:

a cache management store for storing a validation indicator indicating whether the cached result is still valid or not; and

wherein the cache controller is configured for;

determining, in response to the received request, an up-to-date result of the query by;

checking the validation indicator of the cache management store as to whether the cached result is still valid;

if the cached result is still valid, accessing the cache store to obtain the cached result as the up-to-date result;

or

if the cached result is not valid, taking alternative action comprising;

accessing the cache store to obtain the cached result;

determining the records of the schema that have been added or deleted or modified since the step of storing the cached result in the cache store on the basis of the records stored in the differential buffer; and

incrementally deriving the up-to-date result from the cached result and from the records determined in the previous step.

5. The computer system according to claim 1,

wherein the database further comprises:

a cache management store for storing an existence indicator indicating whether the cached result exists or not and a validation indicator indicating whether the cached result is still valid or not; and

wherein the cache controller is configured for;

determining, in response to the received request, an up-to-date result of the query by;

checking the existence indicator of the cache management store as to whether there is a cached result of the query in the cache store, and

if there is a cached result of the query in the cache store, checking the validation indicator of the cache management store as to whether the cached result is still valid;

if the cached result is still valid, accessing the cache store to obtain the cached result as the up-to-date result of the query;

or

if the cached result is not valid, taking alternative action comprising;

accessing the cache store to obtain the cached result;

determining the records of the schema that have been added or deleted or modified since the step of storing the cached result in the cache store on the basis of the records stored in the differential buffer; and

incrementally deriving the up-to-date result from the cached result and from the records determined in the previous step; and

if there is no cached result of the query in the cache store, taking alternative action comprising running the query against the entire schema in order to obtain the up-to-date result.

6. The computer system according to claim 1, wherein the cache controller is further configured for:

selecting one of a plurality of maintenance strategies, wherein the plurality of maintenance strategies includes no materialization, eager full update, eager incremental update, lazy update, smart lazy update, and merge update, wherein the selection is carried out by a cost estimator;

updating the cached result according to the selected maintenance strategy.

7. The computer system according to claim 4, wherein the cache controller is further configured for:

validating the materialized query cached in the cache store in response to an insertion of new records into the differential buffer, by;

determining the records of the schema that have been added or deleted or modified since the step of storing the cached result in the cache store on the basis of the records stored in the differential buffer;

running the query against the records determined in the latter step to obtain a validation result;

checking whether the validation result is empty or not; and

setting the validation indicator of the cache management store to invalid if the validation result is not empty.

8. The computer system according to claim 1, wherein the cache management store contains a cache indicator indicating whether the result of the query is to be cached, wherein the cache controller is configured for:

checking the cache indicator of the cache management store as to whether the result of the query is to be cached or not;

if the result is not to be cached, running the query against the entire schema in order to obtain the up-to-date result; and

if the result is to be cached, taking alternative action comprising determining an up-to-date result.

9. The computer system according to claim 8, wherein the cache controller is further configured for:

generating access statistics regarding the cached results, and storing them in the cache management store; and

setting the cache indictor of the cache management store to caching or no caching in dependence of generated access statistics.

10. The computer system according to claim 1, wherein the database further comprise a current record validity store and a cached record validity store for storing the record validity store at the time of creating or updating the cached result, and wherein the cache controller is further configured for:

comparing the cached record validity store with the current record validity store to determine all records in the main store that have been invalidated; and

taking these invalidated records into account when determining an up-to-date result of the query or when replacing the cached result of the query.

11. The computer system according to claim 1, wherein the cache controller comprises a garbage collection mechanism for eliminating cached queries that are not worthwhile to store in the cache store.

12. A method implemented in a computer system for both online transaction processing and online analytical processing, comprising the steps of:

providing the computer system that comprises;

a processor;

a database coupled to the processor, the database comprising;

a main store for storing records,

a differential buffer for receiving and buffering records, the differential buffer being coupled to the main store,

a schema comprising records stored in the main store and records stored in the differential buffer, and

a cache store for caching a result of a query against the schema, wherein the query involves calculating an aggregate function; and

a cache controller executable by the processor and communicatively coupled to the database; and

the cache controller performing the following steps;

storing a result of the query in the cache store as a materialized aggregate;

receiving an analytical request; and

determining, in response to the received request, an up-to-date materialized aggregate by;

accessing the cache store to obtain the cached materialized aggregate;

determining the records of the schema that have been added or deleted or modified since the step of storing the cached result in the cache store on the basis of the records stored in the differential buffer; and

incrementally deriving the up-to-date materialized aggregate from the cached materialized aggregate and from the records determined in the previous step,

wherein the cache controller is further configured for updating the cached materialized aggregate according to a merge update strategy, the merge update strategy comprising;

replacing the cached materialized aggregate in the cache store in response to the computer system initiating a merge of the records of the differential buffer into the main store, by;

accessing the cached query from the cache store during the merge;

determining the records of the schema that have been added or deleted or modified since the step of storing the cached result in the cache store on the basis of the records stored in the differential buffer;

incrementally deriving the up-to-date materialized aggregate from the cached materialized aggregate and from the records determined in the previous step; and

replacing the cached materialized aggregate in the cache store with the up-to-date materialized aggregate.

13. A non-transitory computer-readable medium comprising a plurality of machine-readable instructions that are executed by a computer system having one or more processors, the computer system including a processor, a database, and a cache controller, the database comprising a main store for storing records, a differential buffer for receiving and buffering records, a schema comprising records stored in the main store and records stored in the differential buffer, and a cache store for caching a result of a query against the schema, wherein the query involves calculating an aggregate function, and wherein the machine-readable instructions control the cache controller to perform the following steps:

storing a result of the query in the cache store as a materialized aggregate;

receiving an analytical request; and

determining, in response to the received request, an up-to-date materialized aggregate by;

accessing the cache store to obtain the cached materialized aggregate;

determining the records of the schema that have been added or deleted or modified since the step of storing the cached result in the cache store on the basis of the records stored in the differential buffer; and

incrementally deriving the up-to-date materialized aggregate from the cached materialized aggregate and from the records determined in the previous step,

wherein the cache controller is further configured for updating the cached materialized aggregate according to a merge update strategy, the merge update strategy comprising;

replacing the cached materialized aggregate in the cache store in response to the computer system initiating a merge of the records of the differential buffer into the main store, by;

accessing the cached query from the cache store during the merge;

determining the records of the schema that have been added or deleted or modified since the step of storing the cached result in the cache store on the basis of the records stored in the differential buffer;

incrementally deriving the up-to-date materialized aggregate from the cached materialized aggregate and from the records determined in the previous step; and

replacing the cached materialized aggregate in the cache store with the up-to-date materialized aggregate.