Query Routing via Storage Access Cost in Tiered Databases

Solution Overview

Problem

Existing database management systems face challenges in efficiently routing database queries across different database tiers, leading to suboptimal performance due to storage bottlenecks and complex query planning.

Innovation Solution

A tiered database system is implemented, comprising a data warehouse, a relational layer, and a proxy layer. The query planner computes a storage access cost based on database metrics and query plans, routing queries to either the data warehouse or the relational layer depending on the cost threshold.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Quantity of substance

If queries are routed to the data warehouse for large-scale data storage, then storage capacity is improved, but query processing time increases due to data retrieval distance

Engineering Contradiction:

Improvestorage capacityVSAvoidquery processing time

Core Design Contradiction:

Quantity of substanceVSSpeed

Solution Approach 1:

The database system is segmented into multiple tiers (data warehouse tier and relational database tier) with different storage characteristics and access speeds. The query router divides queries into two categories based on storage access cost: those requiring large storage capacity are routed to the data warehouse, while those requiring fast access are routed to the relational database, resolving the contradiction between storage capacity and query processing speed

Inventive Principle:

Principle #1Segmentation

Solution Approach 2:

A query router is introduced as an intermediary component between the user and the database tiers. The query router calculates storage access costs for different query types and makes intelligent routing decisions, acting as a mediator that balances the trade-off between storage capacity and query processing time by directing queries to the most appropriate tier

Inventive Principle:

Principle #24Intermediary (Mediator)

2Device complexity

If a simple single-tier database is used, then device complexity is reduced, but routing optimization capability is lost

Engineering Contradiction:

Improvedatabase structure complexityVSAvoidquery routing optimization

Core Design Contradiction:

Device complexityVSProductivity

Solution Approach 1:

The database system is segmented into multiple tiers (data warehouse tier and relational database tier) with different storage characteristics and access speeds. The query router divides queries into two categories based on storage access cost: those requiring large storage capacity are routed to the data warehouse, while those requiring fast access are routed to the relational database, resolving the contradiction between storage capacity and query processing speed

Inventive Principle:

Principle #1Segmentation

Solution Approach 2:

A query router is introduced as an intermediary component between the user and the database tiers. The query router calculates storage access costs for different query types and makes intelligent routing decisions, acting as a mediator that balances the trade-off between storage capacity and query processing time by directing queries to the most appropriate tier

Inventive Principle:

Principle #24Intermediary (Mediator)

3Productivity

If queries are routed based on storage access cost, then query performance is improved, but routing decision complexity increases

Engineering Contradiction:

Improvequery performanceVSAvoidrouting decision complexity

Core Design Contradiction:

ProductivityVSDevice complexity

Solution Approach 1:

The query router changes the routing parameter from simple connection routing to storage access cost-based routing. By calculating and comparing storage access costs for different query types, the system makes optimized routing decisions that improve query performance while keeping the routing logic manageable through parameter-based decision making

Inventive Principle:

Principle #35Parameter changes

Data Source

PatentUS20250077522A1Intelligent Query Routing based on Query Storage Cost for Tiered Databases

Publication Date: 2025.03.06 ZETTABLOCK INC

AI summary

Systems and methods for optimizing database routing in accordance with embodiments of the invention are illustrated. One embodiment includes a tiered database. The tiered database includes a data warehouse used to store obtained data. The tiered database includes a relational layer that stores a copy of a subset of the obtained data within a certain period of recency. The tiered database includes a query planner. The query planner is configured to receive a new query and determine a query plan to respond to the query. The query planner is configured to compute, from a set of database metrics and the preliminary query plan, a storage access cost. The query planner is configured to route the query based on the storage access cost, wherein the query is routed: to the data warehouse when the storage access cost exceeds a predetermined threshold; and to the relational layer otherwise.