Database Cache Table for Fast Data Retrieval

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

As databases grow in size, the time required to retrieve and load data within applications increases, and existing methods for expediting this process, such as pre-fetching data or caching query results, often have limitations that hinder efficient data retrieval and display, especially for applications that retrieve data in chronological order like Last-In First-Out (LIFO).

Innovation Solution

Implementing a cache table within the database that associates a session identifier with a sequence identifier, where a sequencer generates sequence identifiers for data records, allowing the application to efficiently retrieve the last-in-time data records by accessing the cache table and data tables based on these identifiers, and automatically re-populating the cache table if needed.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Speed

If data is pre-fetched during application launch or query results are stored in application cache, then data retrieval speed is improved, but memory usage increases and cache management complexity increases

Engineering Contradiction:

Improvedata retrieval speedVSAvoidmemory usage

Core Design Contradiction:

SpeedVSQuantity of substance

Solution Approach 1:

The patent extracts the sequence tracking functionality from the application layer and places it directly in the database layer through a sequencer object. This allows the application to retrieve data by simple sequence ranges without needing to pre-fetch or cache sequence information, reducing application memory usage while maintaining fast retrieval speeds.

Inventive Principle:

Principle #2Taking out (Extraction)

Solution Approach 2:

The patent introduces a cache table as an intermediary structure between the database and application. This cache table stores sequence-to-record mappings, allowing the application to efficiently determine which records fall within a desired sequence range without loading all data into application cache, thus reducing memory usage while maintaining retrieval speed.

Inventive Principle:

Principle #24Intermediary (Mediator)

2Speed

If data is pre-fetched during application launch, then data retrieval speed is improved, but data freshness decreases and latency increases

Engineering Contradiction:

Improvedata retrieval speedVSAvoiddata latency

Core Design Contradiction:

SpeedVSLoss of time

Solution Approach 1:

The patent performs preliminary actions by having the database automatically generate sequence identifiers for records as they are inserted. This preliminary sequencing allows the application to later retrieve records by simple range queries without needing to pre-fetch data, ensuring both fast retrieval and data freshness since records are retrieved on-demand rather than pre-loaded.

Inventive Principle:

Principle #10Preliminary action

3Productivity

If a cache table associates session identifier with sequence identifier, then data retrieval efficiency is improved, but device complexity increases

Engineering Contradiction:

Improvedata retrieval efficiencyVSAvoidcache table complexity

Core Design Contradiction:

ProductivityVSDevice complexity

Solution Approach 1:

The patent segments the data retrieval process into distinct components: a sequencer that generates sequence identifiers, a cache table that stores sequence-to-session mappings, and a retrieval mechanism that queries by sequence ranges. This segmentation allows each component to remain simple while the overall system achieves high retrieval efficiency through their coordinated interaction.

Inventive Principle:

Principle #1Segmentation

4Measurement precision

If sequence identifiers are generated for each data record, then data retrieval precision is improved, but device complexity increases

Engineering Contradiction:

Improvedata retrieval precisionVSAvoidsequencer complexity

Core Design Contradiction:

Measurement precisionVSDevice complexity

Solution Approach 1:

The patent implements self-service by having the database system automatically generate and manage sequence identifiers through a sequencer object. This eliminates the need for application code to manually manage sequencing logic, reducing application complexity while providing precise control over which records are retrieved based on sequence ranges.

Inventive Principle:

Principle #25Self-service

Data Source

PatentUS11243952B2Data cache using database trigger and programmatically resetting sequence

Publication Date: 2022.02.08 BANK OF AMERICA CORP

AI summary

Expedition of retrieval of data from a database and loading of the retrieved data in an associated application. A cache table within the database is provided that associates a session identifier (i.e., a data record identifier) with a sequence identifier. As data records are received into the database, a sequencer is triggered that generates a sequence identifier for the data record and an entry is created in the cache table that associates the sequence identifier with the session identifier. Data is retrieved from the database and populated within the application, by (i) accessing the sequencer within the database to determine the last-in-time sequence identifier, (ii) accessing the cache table to determine a range of cache table entries based on the last-in-time sequence identifier, and (iii) retrieving the data records in the applicable data tables based on the associated session identifiers in the cache table entries.