Database Bulk Insert Mode Switching for Massive Data Loading
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems face inefficiencies in large-scale data loading due to serial inserts and index updates in non-BULK insert mode, leading to wasted time and resources, even when using multiple threads.
Innovation Solution
Converting serial inserts to BULK insert mode, where rows are accumulated into row buffers the size of data pages and written to disk in parallel, allowing for simultaneous index updates, thereby reducing the frequency of resource allocation and index updates.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If serial inserts are used to load data row-by-row, then data integrity and index updates are maintained, but loading efficiency and productivity are severely reduced
Solution Approach 1:
The patent segments the data loading process into distinct phases: a warm-up phase that loads initial rows with full index updates to establish baseline performance, and a bulk phase that loads subsequent rows using batched operations with reduced index update frequency. This segmentation allows the system to balance between maintaining data integrity and improving loading efficiency for large datasets.
Solution Approach 2:
The patent implements periodic index updates during bulk loading operations, where indexes are updated at regular intervals rather than after every row insertion. This periodic action reduces the overhead of continuous index maintenance while still ensuring data integrity, thereby improving overall productivity during massive data loading scenarios.
2Productivity
If multiple threads are used for parallel data loading, then throughput increases, but resource allocation overhead and complexity increase
Solution Approach 1:
The patent merges multiple thread operations into coordinated bulk loading phases, where multiple threads work in parallel during the warm-up and bulk phases but are synchronized at key checkpoints. This merging approach maintains high throughput while reducing the complexity of thread management by establishing clear phase boundaries and coordination points.
Solution Approach 2:
The patent introduces a coordinator mechanism that acts as an intermediary between multiple loading threads and the index management system. This intermediary consolidates index update requests from multiple threads and processes them in a coordinated manner, reducing thread management complexity while maintaining parallel throughput.
3Reliability
If indexes are updated after every row insertion, then data integrity is maintained, but resource usage and operation frequency increase
Solution Approach 1:
The patent implements periodic index updates during bulk loading operations, where indexes are updated at regular intervals rather than after every row insertion. This periodic action reduces the overhead of continuous index maintenance while still ensuring data integrity, thereby improving overall productivity during massive data loading scenarios.
Solution Approach 2:
The patent performs preliminary index updates during the warm-up phase before bulk loading begins, establishing the index structure in advance. This preliminary action reduces the frequency of index updates required during subsequent bulk loading operations, thereby reducing resource usage while maintaining data integrity.
Data Source
AI summary
A method can include initiating execution of a database command, the database command associated with a base table with at least one row to copy to a target table, the database command associated with a non-bulk insert mode; making a run-time decision on whether to automatically convert the insert mode from the non-bulk insert mode to a BULK insert mode based on the number of row buffers filled with rows from the base table during execution of the database command; and inserting at least one row into the target table using an insert mode based on the run-time decision.