Dual Buffer Database for Transactional Query Throughput
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current BI databases face challenges in meeting the high throughput query requirements of transactional services while storing data in a column manner, as they are optimized for analytical services, leading to inefficiencies and increased complexity in maintaining separate databases for different service types.
Innovation Solution
A database system with both column and row buffers is implemented, allowing for efficient querying of transactional service data by loading column data into a row buffer and establishing a correspondence between column identifiers and row indices, enabling direct querying and reducing the need for separate databases for analytical and transactional services.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If data is stored in a column manner in a BI database, then analytical service query speed is improved, but transactional service query throughput deteriorates
Solution Approach 1:
The database memory is segmented into two distinct buffers: a column buffer for storing columnar data structures optimized for analytical queries, and a row buffer for storing row-oriented data structures optimized for transactional queries. This segmentation allows each buffer to serve its specific purpose without interfering with the other, resolving the contradiction between analytical query speed and transactional query throughput
Solution Approach 2:
The system dynamically switches between columnar and row-oriented data access modes based on the service type. For analytical services, the system accesses data through the column buffer in columnar format; for transactional services, it accesses data through the row buffer in row-oriented format. This dynamic adaptation allows the same database to optimize performance for different query patterns
2Speed
If separate databases are used for analytical and transactional services, then query performance for each service type is improved, but database system complexity increases
Solution Approach 1:
The patent merges the functionality of separate analytical and transactional databases into a single BI database by implementing both column and row buffers within the same database memory. This combination allows the system to maintain optimized performance for both service types while reducing the complexity of managing multiple separate databases, data synchronization protocols, and associated infrastructure
Solution Approach 2:
The BI database is designed with multi-functionality to serve both analytical and transactional services simultaneously. By incorporating dual buffer structures and supporting both columnar and row-oriented data access patterns, the single database system can handle diverse query requirements without requiring separate specialized databases for each service type
3Productivity
If data is stored in a row manner to meet high throughput requirements, then transactional service throughput is improved, but analytical service query speed deteriorates
Solution Approach 1:
The database memory is segmented into two distinct buffers: a column buffer for storing column data of the database, and a row buffer for storing column data in row format. This segmentation allows the system to maintain row-oriented storage for high throughput transactional services while preserving column-oriented storage for fast analytical queries
Solution Approach 2:
The system changes the data access parameter (storage orientation) based on the service type. For transactional services requiring high throughput, the system accesses data through the row buffer in row-oriented format. For analytical services requiring fast query performance, the system accesses data through the column buffer in column-oriented format
Data Source
Figure 1
Figure 2
Figure 3
AI summary
A method and an apparatus for querying service data are disclosed. A database memory includes a column buffer and a row buffer, a column of the column buffer is used to load column data of a database, and a row of the row buffer is used to load column data of the column buffer or the column data of the database. When a transactional service data query request that is sent by a transactional service system and that includes a target column identifier and a first query condition is received, it is detected whether a row index corresponding to the target column identifier exists, and if yes, column data that corresponds to the target column identifier, and that is in a row that is in the row buffer and corresponds to the row index is queried based on the first query condition, to obtain a query result.