Synthetic Relational Operation for Database Join Processing
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional database management systems face inefficiencies in query processing, particularly when handling large numbers of queries, due to repeated disk access, excessive memory usage, and limitations in caching and pipelining methods, which lead to decreased query processing speed.
Innovation Solution
A query processing system utilizing multi-operation processing with synthetic relational operations, which converts queries into query trees, groups tasks by relation, collects common sub-expressions, and creates synthetic operations to reduce disk access and optimize memory usage through virtual relations and simultaneous processing of tasks.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If conventional query processing accesses data from disk for each query operation, then data can be processed, but disk access frequency increases and query processing speed decreases
Solution Approach 1:
The system performs preliminary actions by caching query results and intermediate operation results in memory before they are needed. When a query is executed, the system checks if results are already cached and retrieves them from memory instead of accessing disk, thereby reducing disk access time and improving query processing speed.
Solution Approach 2:
The system implements pipelining to maintain continuous useful action by processing multiple queries simultaneously through overlapping execution stages. Intermediate results are kept in memory and reused across multiple queries, ensuring that processing continues without interruption rather than completing one query entirely before starting the next, thus reducing overall disk access time.
2Speed
If query results are cached in memory to reduce disk access, then query processing speed improves, but memory usage increases
Solution Approach 1:
The system changes parameters by implementing a hierarchical caching strategy with different cache sizes and replacement policies for different types of data (query results, intermediate results, relation data). This allows the system to optimize memory usage by allocating different amounts of memory to different caching needs based on access patterns and data importance, thereby improving query processing speed without excessive memory consumption.
3Productivity
If multiple hash tables are created in main memory for join operations, then join processing capability improves, but available main memory space decreases and query processing speed decreases
Solution Approach 1:
The system applies segmentation by dividing large hash tables into smaller segments that can be processed in batches. Instead of creating one large hash table for all join operations, the system creates multiple smaller hash tables for different relation pairs or different portions of the data, allowing efficient join processing while managing memory usage through controlled allocation of memory segments.
Solution Approach 2:
The system uses periodic action by implementing a hash table creation and destruction cycle for join operations. Hash tables are created only when needed for specific join operations, used for the duration of the join processing, and then destroyed to free memory. This periodic creation and destruction allows the system to maintain high join processing capability while ensuring memory is released for subsequent operations.
4Productivity
If query processing creates intermediate results on disk for each task, then processing can be completed, but the number of disk accesses increases and query processing speed decreases
Solution Approach 1:
The system introduces an intermediary layer in the form of memory-based intermediate result storage. Instead of writing intermediate results directly to disk, the system uses memory as an intermediary buffer to store intermediate results temporarily. This allows subsequent tasks to access intermediate results from memory rather than disk, reducing disk access time while still completing the full query processing pipeline.
Data Source
AI summary
A query processing system of a database using multi-operation processing includes a task retrieval unit for retrieving relational algebra as tasks from a query tree, a unit for dividing retrieved tasks into groups for each relation of database, a synthetic relational operation unit for creating a synthetic relational operation by collecting tasks having common sub-expression for the grouped tasks into sub-groups and creating a task of a synthetic relational operation for each sub-group, wherein when there is a plurality of tasks of join operation in the grouped tasks, the synthetic relational operation unit further divides the grouped tasks into sub-groups for each common sub-expression of join operation, collects a plurality of tasks of the join operation into a sub-group, and creates a single task of a synthetic join operation by using logical union to concatenate each task of the join operation collected in the sub-group.