Pipelined Database Query Processing for Memory-Constrained Insert Operations
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database systems face memory constraints when copying large data from a remote source table to a target table, especially in cloud environments where limited or expensive memory is available, leading to inefficient use of resources.
Innovation Solution
Implementing a pipelined search method that allocates a pre-defined chunk size of memory, allowing for iterative fetching and insertion of data from the source table into the target table, reducing memory usage while maintaining performance comparable to bulk transfers.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If the database system allocates memory equal to the entire source table to fetch and insert data, then the data transfer operation can be completed efficiently in a single batch, but the memory consumption becomes excessively high, especially in cloud environments with limited or expensive memory resources
Solution Approach 1:
The patent divides the large source table data into smaller chunks or batches. Instead of loading the entire source table into memory at once, the system processes data in segmented portions, fetching and inserting a batch of rows at a time. This segmentation allows the database to complete the data transfer operation efficiently while keeping memory consumption at manageable levels throughout the process.
2Quantity of substance
If the database system uses a pipelined approach to fetch and insert data in smaller batches, then memory usage is reduced to manageable levels, but the operation requires multiple fetch and insert cycles which may seem to increase processing time
Solution Approach 1:
The patent implements a continuous pipelined operation where data fetching, processing, and inserting occur in an ongoing stream rather than discrete batch operations. The system maintains continuous data flow from the source table through memory to the target table, eliminating idle time between batches. This continuous action ensures that while memory usage remains low due to batched processing, the overall processing time does not increase significantly compared to bulk operations.
3Quantity of substance
If the database system processes data in iterative chunks with fetch-store-clear cycles, then memory resources are optimized for cloud environments, but the complexity of the data transfer operation increases
Solution Approach 1:
The patent implements an automated pipelined data transfer system that self-manages the chunked processing workflow. The database system automatically handles the iterative fetch-store-clear cycles without requiring manual intervention or complex external coordination. The system self-regulates memory allocation, automatically processes data chunks in sequence, and manages the transfer operation autonomously, thereby optimizing memory resources while keeping the operational complexity manageable through automation.
Data Source
AI summary
In some embodiments, a method receives a query to retrieve data from a source table and insert the data into a target table and allocates a size of memory. A portion of the data from the source table is retrieved based on the size of memory and stored in the memory. Then, the method stores the retrieved portion of the data stored in the memory into the target table and continues to retrieve portions of data from the source table in increments based on the size of memory, store the retrieved portions of data in the memory, and store the retrieved portions of data from the memory in the target table until the data from the source table has been inserted into the target table.