Dual Database Query Routing for IO Cost Reduction
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database management systems face inefficiencies in handling large data volumes and complex queries due to the normalization process, leading to increased IO costs and limitations in data representation, which are exacerbated by the need for multiple data representations and high maintenance costs of OLAP cubes.
Innovation Solution
Implementing a dual database management system architecture that includes both columnar and non-columnar DBMS, where queries are routed based on execution time estimates and data formats to optimize query performance, potentially eliminating the need for data marts and OLAP cubes.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If data is stored in normalized row-oriented structure in HDS, then data can be maintained with referential integrity, but IO cost increases significantly for analytical queries
Solution Approach 1:
The patent segments data storage into two distinct systems: HDS maintains normalized row-oriented data for transactional integrity, while a separate columnar database stores denormalized data optimized for analytical queries. This segmentation allows each system to specialize in its strength without compromising the other.
Solution Approach 2:
The patent creates a copy of the normalized data from HDS and transforms it into a columnar format in a separate database system. This copying approach allows analytical queries to operate on the columnar copy without affecting the original normalized data structure, thereby reducing IO costs while preserving referential integrity in the source system.
2Adaptability or versatility
If multiple data representations (DW, DMs, cubes) are created to address analytical needs, then query flexibility improves, but system complexity and maintenance costs increase
Solution Approach 1:
The patent makes the columnar database universal by designing it to handle multiple analytical query types directly on the denormalized data without requiring separate structures for different query patterns. This single multi-functional system replaces the traditional multi-structure approach (DWs, DMs, and cubes), reducing overall system complexity while maintaining query flexibility.
Solution Approach 2:
The patent extracts the analytical processing function from the traditional complex multi-structure system and consolidates it into a single columnar database system. By taking out the analytical workload from the normalized HDS and handling it in the specialized columnar system, the patent simplifies the overall architecture while preserving analytical capabilities.
3Loss of time
If star schema structures are used to reduce join operations, then query execution time improves, but the number of rows in fact tables grows over time increasing IO burden
Solution Approach 1:
The patent changes the fundamental parameter of data organization from row-oriented to columnar format. This parameter change fundamentally alters how data is stored and accessed, enabling efficient analytical queries without the row proliferation problem inherent in star schema fact tables. The columnar format naturally handles growing data volumes more efficiently.
4Loss of time
If OLAP cubes are used to precalculate aggregated results, then query response time improves, but additional costs and latency are introduced for cube maintenance
Solution Approach 1:
The columnar database system provides self-service capabilities for analytical queries by directly querying the denormalized data without requiring pre-calculated cubes. The system serves analytical needs directly from the stored data, eliminating the need for separate cube maintenance processes and their associated overhead.
Data Source
AI summary
The same data is stored in at least two different database management systems (DBMS's), including a columnar DBMS (320.2) and a non-columnar (e.g. row-oriented) DBMS (320.1). The columnar DBMS may provide fast responses to queries involving aggregation, and such queries can be routed to the columnar DBMS. A query may be routed to the non-columnar DBMS if for example the query execution involves only small DBMS tables on the non-columnar DBMS. In some situations, an execution time estimate can be obtained from each DBMS, and the query is routed to the DBMS providing the shortest estimate.