In-Memory Cursor Duration Temp Tables for Database Query Performance
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In multi-node database systems, storing temporary tables on shared disk leads to significant overhead due to allocation overhead and slower disk access compared to volatile memory, causing delays during query processing, especially for small results.
Innovation Solution
Storing cursor duration temporary tables in local volatile memory of nodes, where each node allocates memory segments for writer and reader threads to process and share temporary data, reducing the need for disk access and merging delays.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If temporary tables are stored on shared disk, then data can be shared across multiple nodes, but access speed decreases and overhead increases
Solution Approach 1:
The patent divides the temporary table storage into multiple segments distributed across different nodes. Each node stores a portion of the temporary table in its local memory, allowing parallel access while maintaining data sharing capabilities. This segmentation eliminates the single-point bottleneck of shared disk storage.
Solution Approach 2:
The patent transitions from a centralized shared disk storage model to a distributed in-memory storage model across multiple nodes. By adding the dimension of distributed memory storage, the system achieves both fast local access and data sharing through inter-node communication.
2Reliability
If temporary tables are stored on shared disk, then data persistence is maintained, but allocation overhead increases
Solution Approach 1:
The patent pre-allocates memory segments for temporary table storage on each node before query execution begins. This preliminary allocation eliminates the need for dynamic allocation during query processing, reducing overhead and improving efficiency.
Solution Approach 2:
The patent creates copies of temporary table data across multiple nodes in memory. Instead of allocating and managing a single shared disk storage location, the system replicates data copies on each node, eliminating complex allocation and sharing logic.
3Productivity
If worker threads write to shared disk segments, then parallel processing is enabled, but merging delays occur
Solution Approach 1:
The patent divides the temporary table into multiple segments distributed across different nodes, allowing worker threads to write to local segments in parallel without requiring centralized merging. Each node independently manages its segment, eliminating the serial merge bottleneck.
Solution Approach 2:
The patent extracts the merging operation from the system by allowing each node to independently write to its own segment. The segments are already in their final positions, eliminating the need for a separate merging step that causes delays.
4Quantity of substance
If disk-based temporary tables are used, then storage capacity is sufficient, but read/write performance decreases
Solution Approach 1:
The patent changes the storage medium parameter from disk-based to in-memory storage. By transitioning from magnetic/disk storage to volatile memory, the system achieves dramatically faster read/write performance while maintaining sufficient storage capacity for temporary tables.
Data Source
AI summary
Techniques are provided herein for processing a query using in-memory cursor duration temporary tables. The techniques involve storing a part of the temporary table in memory of nodes in a database cluster. A part of the temporary table may be stored in disk segments of nodes in the database cluster. Writer threads running on a particular node writes data for the temporary table to the memory of the particular node. Excess data may be written to the disk segment of the particular node. Reader threads running on the particular node reads data for the temporary table from the memory of the particular node and the disk segment of the particular node.