Database Query Processing via Record ID Pooling
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems face inefficiencies in query processing due to the high number of disk Input/Output (I/O) operations, which increase costs and reduce performance, especially when handling a large volume of queries sequentially.
Innovation Solution
A method where a database system stores record identification numbers in a pool in numerical order based on an execution plan, allowing for batch processing of data records corresponding to these IDs, optimizing disk I/O operations by distributing and retrieving data records efficiently.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If queries are processed sequentially in traditional database systems, then query processing can be performed using standard execution plans, but the number of disk I/O operations increases significantly, reducing performance and increasing costs
Solution Approach 1:
The patent merges multiple individual query execution plans into a single batched execution plan. By collecting record ID numbers from multiple queries and processing them together in batches, the system reduces the total number of disk I/O operations. Instead of executing each query separately and performing redundant I/O operations, the merged batched execution plan retrieves data records in larger chunks, thereby improving productivity while reducing energy loss through fewer I/O operations.
Solution Approach 2:
The patent applies preliminary action by pre-collecting and organizing record ID numbers from multiple queries before executing the batched processing. The system gathers all necessary record IDs, sorts them, and prepares the batched execution plan in advance. This preliminary organization allows the subsequent disk I/O operations to be more efficient and targeted, reducing the overall number of I/O operations needed while maintaining high query processing efficiency.
2Loss of energy
If batch processing is implemented to reduce disk I/O operations, then query processing costs decrease and performance improves, but the complexity of the execution plan increases
Solution Approach 1:
The patent applies segmentation by dividing the batched execution into manageable segments or batches. Instead of creating one overly complex execution plan for all queries, the system segments the record ID numbers into smaller batches that can be processed sequentially. Each batch has its own simplified execution plan, which reduces the overall complexity while still achieving the benefit of reduced disk I/O operations compared to traditional sequential processing.
3Productivity
If record ID numbers are stored in a pool in numerical order, then data records can be batched efficiently based on distribution, but additional processing steps are required to organize and manage the pool
Solution Approach 1:
The patent applies preliminary action by pre-sorting and organizing record ID numbers into a pool in numerical order before batched processing begins. This preliminary organization enables efficient batching based on the distribution of record IDs, allowing the system to retrieve data records in optimized sequences. The upfront sorting effort is offset by the significant improvements in data retrieval efficiency during the actual query execution phase.
Data Source
AI summary
A method for processing a plurality of queries is provided according to embodiments of the present disclosure. In this method, based on a plurality of queries and an execution plan for the plurality of quires, a plurality of record identification (ID) numbers can be stored into a pool in a numerical order. Each of the plurality of record ID numbers can identify a data record in a database. Then, the execution plan can be performed to batch a plurality of data records corresponding to the plurality of record ID numbers in the database based on a distribution of the plurality of record ID numbers in the pool.