Canonical Table Repository for Database Query Latency Reduction

Solution Overview

Problem

Modern businesses face challenges in efficiently accessing and manipulating large datasets stored in cloud-based data warehouses due to the complexity of database queries and the associated costs and latency in waiting for responses.

Innovation Solution

The system maintains a canonical table repository of transformed tables, allowing it to service database requests by generating a description of the requested table and determining if it is a subset of existing canonicalized tables, thereby transforming and providing the required data without issuing a new database query, thus reducing latency and costs.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Reliability

If a new database query is issued to retrieve a table, then the requested data can be obtained from the cloud-based data warehouse, but the system incurs additional costs and experiences latency while waiting for the response

Engineering Contradiction:

Improvedata availabilityVSAvoidquery response latency

Core Design Contradiction:

ReliabilityVSLoss of time

Solution Approach 1:

The system performs preliminary actions by maintaining a local cache of canonicalized tables that were previously retrieved from the database. When a query is received, the system first checks whether the requested table or a superset of it already exists in the cache, thereby avoiding the need to issue a new database query and reducing latency

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The system creates canonicalized copies of database tables and stores them in a local cache. These canonicalized tables are transformed versions of the original tables that can be efficiently queried and manipulated locally, eliminating the need for repeated database queries while maintaining data availability

Inventive Principle:

Principle #26Copying

2Reliability

If a new database query is issued to retrieve a table, then the requested data can be obtained from the cloud-based data warehouse, but the system incurs additional costs

Engineering Contradiction:

Improvedata availabilityVSAvoidquery execution cost

Core Design Contradiction:

ReliabilityVSLoss of energy

Solution Approach 1:

The system performs preliminary actions by maintaining a local cache of canonicalized tables that were previously retrieved from the database. When a query is received, the system first checks whether the requested table or a superset of it already exists in the cache, thereby avoiding the need to issue a new database query and reducing costs

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The system creates canonicalized copies of database tables and stores them in a local cache. These canonicalized tables are transformed versions of the original tables that can be efficiently queried and manipulated locally, eliminating the need for repeated database queries while maintaining data availability

Inventive Principle:

Principle #26Copying

3Productivity

If the system maintains a canonical table repository with transformed tables, then database requests can be serviced locally without issuing new queries, but the system requires additional memory and storage resources

Engineering Contradiction:

Improverequest servicing efficiencyVSAvoidcache memory usage

Core Design Contradiction:

ProductivityVSVolume of stationary object

Solution Approach 1:

The system applies local quality by canonicalizing tables locally in the cache, transforming them into a standardized format that facilitates efficient local querying and manipulation. This allows the system to service requests using only the portions of data that are already cached, reducing the need to store entire large tables while maintaining productivity

Inventive Principle:

Principle #3Local quality

Solution Approach 2:

The system segments the database tables into canonicalized portions that are stored in the local cache. By dividing the data into manageable segments and storing only the necessary portions, the system reduces memory usage while maintaining the ability to service common requests efficiently

Inventive Principle:

Principle #1Segmentation

Data Source

PatentUS11494400B2Servicing database requests using subsets of canonicalized tables

Publication Date: 2022.11.08 SIGMA COMPUTING INC

AI summary

Servicing database requests using subsets of canonicalized tables including maintaining a canonical table repository of canonicalized tables, wherein each canonicalized table is a transformed version of a table previously retrieved from a database; receiving, from a client computing system, a request for a table from the database; generating a description of a canonical version of the requested table; determining that the canonical version of the requested table is a subset of a canonicalized table in the canonical table repository; and in response to determining that the canonical version of the requested table is a subset of the canonicalized table in the canonical table repository: transforming the canonicalized table containing a superset of the canonical version of the requested table based on the received request for the table; and providing, to the client computing system, the transformed canonicalized table containing the superset of the canonical version of the requested table.