Hybrid Query Execution Using Delta Records for Consistent Analytics
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing query execution processes in hybrid database systems are hindered by the need to synchronize large volumes of data changes between transactional and analytical engines, leading to replication delays and timeouts, especially in update-heavy workloads.
Innovation Solution
A method that involves synchronizing source and target database systems using a replication system, storing delete and insert records in a storage device, and executing queries on a consistent view by transforming them into target and delta queries, thereby avoiding replication delays.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If data is synchronized from source database to target database using replication system, then data consistency is improved, but query execution time increases due to replication delays and timeouts
Solution Approach 1:
The patent segments the query execution process into two independent parts: (1) executing the query on the target database table, and (2) executing a delta query on the change records stored in the storage device. This segmentation allows parallel processing and eliminates the need to wait for replication completion, thereby reducing query execution time while maintaining data consistency through the combined results of both queries.
Solution Approach 2:
The patent implements preliminary action by storing change records (delete and insert records) in a storage device during the synchronization process. These pre-stored change records enable the system to execute delta queries quickly without waiting for replication to complete, thus reducing query execution time while ensuring data consistency is maintained through the replication system.
2Reliability
If replication system processes large volumes of data changes, then data synchronization is improved, but system performance deteriorates due to replication delays
Solution Approach 1:
The patent segments the data change processing into two independent paths: (1) replication of changes from source to target database, and (2) execution of delta queries on stored change records. This segmentation allows both processes to run in parallel, improving system performance by eliminating the sequential dependency between replication and query execution, while maintaining data synchronization reliability.
Solution Approach 2:
The patent performs preliminary action by storing change records in a storage device during synchronization, enabling queries to be executed on pre-stored data without blocking the replication process. This preliminary storage of change records allows the system to handle large volumes of data changes efficiently, improving both synchronization reliability and system performance.
3Speed
If query executes on target table including unsynchronized records, then query speed is improved, but data accuracy deteriorates due to replication delays
Solution Approach 1:
The patent segments the query into two parts: a first query executed on the target table (providing speed) and a second query executed on stored change records (providing accuracy). The results are combined to produce the final accurate query result. This segmentation allows the system to achieve both query speed and data accuracy by processing data from both the target table and the stored change records in parallel.
Solution Approach 2:
The patent introduces an intermediary mechanism (the storage device containing change records) that mediates between the target database table and the source database changes. This intermediary allows the system to quickly access both the current state of the target table and the pending changes, combining their results to achieve accurate query results without waiting for replication completion, thus maintaining both speed and accuracy.
Data Source
AI summary
The present disclosure relates to a method including repeatedly synchronizing a source table with a target table. The synchronizing includes receiving changes of the source table, the changes indicating delete records to be deleted and/or insert records to be inserted, replicating the changes to the target table, storing in a storage device the delete records and the insert records of the changes; and deleting from the storage device the records of the changes that are applied at the target table. A query referencing data of the source table may be received. A first query and a second query may be derived from the received query. The first query may be executed on the target table excluding the records in the storage device. The second query may be executed on the insert records of the storage device. The results of the executions may be provided to a sender of the query.