Database BULK Insert Mode for Parallel Index Updates
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems face inefficiencies in large-scale data loading due to serial inserts and sequential index updates in non-BULK insert modes, 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 in non-BULK insert mode, then data integrity and index updates are maintained, but loading efficiency and productivity are poor
Solution Approach 1:
The patent merges multiple row insertions into a single BULK insert operation, combining numerous individual insert statements into one collective operation. This allows the system to process multiple rows simultaneously rather than sequentially, dramatically improving data loading efficiency while maintaining index update integrity through a single coordinated update operation.
Solution Approach 2:
The patent performs preliminary actions by accumulating rows in memory buffers before executing the BULK insert operation. Rows are collected and prepared in advance, allowing the system to optimize the insert operation by batching data into optimal units for bulk processing, thereby reducing the total number of operations required and improving overall loading productivity.
2Productivity
If rows are inserted one-by-one with sequential index updates, then data integrity is maintained, but resource allocation frequency increases and efficiency decreases
Solution Approach 1:
The patent combines multiple individual insert operations and index updates into a single BULK insert operation. Instead of executing one insert statement followed by one index update for each row, the system executes one BULK insert statement that handles multiple rows and performs all necessary index updates in a single coordinated operation, thereby reducing operation frequency and improving loading speed.
Solution Approach 2:
The patent creates copies of row data in memory buffers before the actual insert operation. Rows are accumulated in buffer structures that replicate the target table's row format, allowing the system to prepare data for bulk processing without immediately writing to disk or updating indexes. This copying mechanism enables efficient batch processing and reduces the frequency of physical write operations.
3Productivity
If multiple threads are used for parallel processing, then throughput may improve, but thread coordination overhead and resource management complexity increase
Solution Approach 1:
The patent merges the work of multiple potential parallel threads into a single streamlined BULK insert operation. By accumulating rows in memory and executing one collective insert statement, the system eliminates the need for complex thread coordination and synchronization mechanisms, thereby maintaining high throughput while reducing management overhead and complexity.
Data Source
AI summary
A method for inserting rows into a target table can include receiving a database command, the database command associated with a base table with at least one row to copy to a target table; receiving an indication that use of a BULK insert mode is feasible for the database command; based on the indication, and determining that an insert mode for the database command has been converted from a non-bulk insert mode to the BULK insert mode: reading a row from the base table; building the row read from the base table into an allocated row buffer; inserting the row into the target table in the BULK insert mode; and if it is determined that the allocated row buffer is full, updating at least one index in parallel with the inserting.