Multi-row Database Update via Global Temporary Tables

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Traditional database loading and updating processes are inefficient, consuming significant CPU resources and causing logging and locking issues, which lead to delays and redundancies in managing large volumes of data.

Innovation Solution

A multi-row database load and update system that utilizes in-memory global temporary tables to stage data before inserting it into destination tables, allowing for parallel processing and minimizing locking contentions by using a single insertion statement and optimizing unit of work size, thereby reducing CPU usage and logging time.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Productivity

If traditional database loading and updating processes are used, then data can be stored and managed, but significant CPU resources are consumed and logging time increases

Engineering Contradiction:

Improvedata loading and updating speedVSAvoidCPU resource consumption

Core Design Contradiction:

ProductivityVSUse of energy by moving object

Solution Approach 1:

The patent segments the database update process into distinct phases: staging data in global temporary tables, validating data, performing multi-row inserts, and then cleaning up. This segmentation allows parallel processing of multiple rows simultaneously rather than processing row-by-row, significantly improving productivity while reducing overall CPU consumption through optimized batch operations

Inventive Principle:

Principle #1Segmentation

Solution Approach 2:

The patent changes the parameter of data insertion from single-row to multi-row operations. By using multi-row insert statements and processing multiple rows in parallel within global temporary tables, the system achieves faster data loading and updating speeds while reducing the total CPU time required compared to traditional sequential processing

Inventive Principle:

Principle #35Parameter changes

2Productivity

If traditional database updating processes are used, then data updates can be performed, but logging time and locking issues increase

Engineering Contradiction:

Improveupdate processing speedVSAvoidlogging time

Core Design Contradiction:

ProductivityVSLoss of time

Solution Approach 1:

The patent performs preliminary actions by staging all update data in global temporary tables before actually updating the base tables. Data is validated and prepared in advance, allowing for bulk multi-row insert operations that minimize logging time. The global temporary tables hold the update data ready for efficient batch processing, reducing the time spent on individual row logging and locking operations

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The patent merges multiple individual update operations into a single multi-row insert statement. By combining multiple row updates into one atomic operation against the base table, the system reduces the total logging time and minimizes locking contentions that would occur with multiple separate update statements

Inventive Principle:

Principle #5Merging (Combining)

3Productivity

If parallel loading processes are used to load large amounts of data, then loading speed improves, but locking contentions and delays increase

Engineering Contradiction:

Improvedata loading speedVSAvoidlocking management complexity

Core Design Contradiction:

ProductivityVSDevice complexity

Solution Approach 1:

The patent introduces global temporary tables as an intermediary between the parallel loading processes and the base tables. Multiple parallel processes can load data into the global temporary tables simultaneously without causing locking contentions on the base tables. The intermediary staging area allows parallel processing to proceed freely while the actual base table updates are performed as coordinated multi-row operations, simplifying locking management

Inventive Principle:

Principle #24Intermediary (Mediator)

4Speed

If data is loaded directly to destination tables, then indexing can be performed, but locking holds and delays occur

Engineering Contradiction:

Improveindexing speedVSAvoidlocking duration

Core Design Contradiction:

SpeedVSDuration of action of moving object

Solution Approach 1:

The patent performs preliminary data staging and validation in global temporary tables before the actual insert operation to the base table. This preliminary action ensures data readiness and allows for optimized multi-row insert statements that minimize the duration of locking on the base tables. By preparing data in advance in the intermediary staging area, the actual indexing operation can proceed quickly with minimal locking overhead

Inventive Principle:

Principle #10Preliminary action

Data Source

PatentUS8930397B2Multi-row database updating for enterprise workflow application

Publication Date: 2015.01.06 BANK OF AMERICA CORP

AI summary

Embodiments of the invention are directed to a system, method, or computer program product for providing expedited updating of data stored by an entity. Specifically, the invention expedites the updating of data within large quantities of data on database tables. Initially received update data is processed, via multi-row insert, onto in-memory or temporary tables. The update data is staged on a temporary table while the appropriate base table (housing the data to be updated) is determined. Once determined, update data from the temporary table is pointed to the base table. In this way, a massive amount of data updating may occur. This prevents logging and locking associated with adding individual data updates or row updates to the base table independently. Errors are check and processed accordingly. Once updated, the update data on the temporary table is deleted in mass and a check point restart is issued.