Aggregate Query Caching with Differential Buffer
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In-memory database systems with a main-delta architecture face challenges in maintaining up-to-date analytical queries, particularly when handling large amounts of data and multiple parallel user accesses, leading to slower query execution times due to the need for massive parallelization and complex view invalidation strategies that incur high computational costs.
Innovation Solution
A computer system with a cache controller that incrementally derives up-to-date query results by accessing a cache store and combining cached results with records from a differential buffer, reducing the need for duplicate storage and minimizing computational overhead, while also implementing efficient cache management and invalidation strategies to maintain consistency.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If analytical queries are calculated on-the-fly using massive parallelization, then query flexibility and up-to-date results are improved, but query execution time increases and system productivity deteriorates
Solution Approach 1:
The system pre-calculates and stores aggregate results in a cache store before they are needed. When a query is executed, the system retrieves pre-computed results from the cache rather than calculating them on-the-fly, thus maintaining query flexibility while significantly improving execution speed.
Solution Approach 2:
The system divides data into two segments: base data stored in the main store and incremental changes stored in the differential buffer. This segmentation allows the cache to store aggregate results based on base data while only needing to recompute aggregates for the smaller differential portion, improving execution speed without sacrificing flexibility.
2Speed
If materialized aggregates are maintained to speed up query execution, then query response time is improved, but computational overhead and system complexity increase
Solution Approach 1:
The system segments the data into base data (main store) and incremental changes (differential buffer). The cache maintenance mechanism only needs to monitor and recompute aggregates for the differential buffer portion, significantly reducing computational overhead and complexity compared to maintaining all aggregates from scratch.
Solution Approach 2:
Instead of maintaining all aggregate results fully and continuously, the system performs partial maintenance by only tracking and recomputing aggregates for the differential buffer. This partial action approach reduces computational overhead while still providing fast query responses for the majority of data.
3Adaptability or versatility
If the differential buffer grows with every write operation, then write flexibility is improved, but read performance deteriorates due to increased data access requirements
Solution Approach 1:
The system performs preliminary aggregation of the differential buffer data and stores these incremental aggregates in the cache store. When read operations occur, the system combines base aggregates from the cache with incremental aggregates from the differential buffer, maintaining fast read performance even as the differential buffer grows.
Solution Approach 2:
The system merges results from two sources: aggregate results derived from base data stored in the main store and aggregate results derived from incremental changes in the differential buffer. This merging allows the system to maintain fast read performance while supporting continuous write operations that grow the differential buffer.
Data Source
Figure 1
Figure 2
Figure 3
AI summary
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.