Database Schema Transformation Cache for Query Efficiency

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Comparing large datasets recorded using different database schemas is time-consuming and inefficient due to the need for schema reconciliation during searches.

Innovation Solution

A system that determines and applies targeted schema transformations for each sub-range of a date range, transforming only the data of interest to a consistent schema, reducing computational load and storage requirements by eliminating unnecessary data processing and alterations.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Reliability

If data recorded using different schemas is reconciled to enable direct comparison, then data consistency is improved, but processing time increases significantly

Engineering Contradiction:

Improvedata consistencyVSAvoidprocessing time

Core Design Contradiction:

ReliabilityVSLoss of time

Solution Approach 1:

The system pre-computes and stores transformation mappings between different schema versions in a transformation cache. When queries span multiple schemas, the system retrieves pre-computed transformations instead of performing full schema reconciliation, dramatically reducing query processing time while maintaining data consistency.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The system divides the data storage into schema-version-specific segments or partitions. Each partition contains data recorded with a specific schema version, allowing the system to process only relevant partitions for each query rather than reconciling all data, thus reducing processing time while ensuring consistency through targeted transformations.

Inventive Principle:

Principle #1Segmentation

2Reliability

If complete schema transformation is applied to all data, then data consistency is ensured, but computational load increases

Engineering Contradiction:

Improvedata consistencyVSAvoidcomputational load

Core Design Contradiction:

ReliabilityVSUse of energy by moving object

Solution Approach 1:

The system applies schema transformations locally only to the specific data partitions or fields that require consistency, rather than transforming entire datasets. The transformation scope is determined by the query requirements and schema version differences, minimizing computational load while ensuring consistency where needed.

Inventive Principle:

Principle #3Local quality

Solution Approach 2:

The system performs partial schema transformations only on the subset of data necessary for the current query operation, rather than applying complete transformations to all data. This selective approach reduces computational load by avoiding unnecessary transformations on data that doesn't require schema alignment.

Inventive Principle:

Principle #16Partial or excessive action

3Reliability

If all database data is transformed to the most recent schema, then data consistency is improved, but storage requirements increase

Engineering Contradiction:

Improvedata consistencyVSAvoidstorage requirements

Core Design Contradiction:

ReliabilityVSQuantity of substance

Solution Approach 1:

The system maintains multiple schema version partitions, allowing data to be stored in its original schema format without requiring transformation to the most recent schema. Each partition is tagged with its schema version, enabling efficient storage and selective transformation only when queries span multiple versions, thus avoiding increased storage requirements while maintaining consistency.

Inventive Principle:

Principle #1Segmentation

4Reliability

If schema transformations are applied during query processing, then data consistency is ensured, but query response time decreases

Engineering Contradiction:

Improvedata consistencyVSAvoidquery response time

Core Design Contradiction:

ReliabilityVSSpeed

Solution Approach 1:

The system pre-computes transformation mappings and caches them before query execution. During query processing, the system retrieves pre-computed transformations from the cache and applies them directly, avoiding the need to compute transformations in real-time, thus ensuring data consistency while maintaining fast query response times.

Inventive Principle:

Principle #10Preliminary action

Data Source

PatentUS11537568B1Efficient data processing for schema changes

Publication Date: 2022.12.27 WORKDAY INC

AI summary

A system for processing database data includes an interface and a processor. The interface is configured to receive a query for the database data comprising a date range and a data selection criterion. The processor is configured to determine a set of fields of the database data corresponding to a most recent date of the date range; determine a subset of the set of fields of the database data specified by the data selection criterion; determine a set of transformations, where each transformation of the set of transformations corresponds to a field of the subset and a sub-range of the date range; transform the database data to determine transformed database data using the set of transformations; and select data from the transformed database data using the data selection criterion to determine a query response.