Database Query Execution Optimization via Denormalization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Normalized databases, particularly those at level 4NF or 5NF, face significant performance issues and increased computational overhead due to complex JOIN type queries, leading to reduced database performance and inability to work in real-time.
Innovation Solution
The method involves identifying the property with the smallest unique values in a database structure, executing a database query to retrieve only data from this property, and using parallel processing with UNION and INTERSECT operators to optimize data retrieval and reduce computational effort.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Loss of substance
If databases are normalized at level 4NF or 5NF to eliminate duplication and reduce redundancy, then data storage efficiency and consistency are improved, but query execution time and computational overhead increase exponentially
Solution Approach 1:
The patent segments the normalized database tables into multiple denormalized copies, each optimized for specific query patterns. Instead of joining multiple normalized tables, the system creates separate denormalized tables that contain pre-joined data, eliminating the need for complex JOIN operations while maintaining data integrity through version control and timestamp-based validation.
Solution Approach 2:
The patent changes the normalization parameter by dynamically adjusting the degree of normalization based on query requirements. The system maintains both normalized and denormalized versions of data, selecting the appropriate representation based on whether the query prioritizes data consistency (normalized) or execution speed (denormalized), thereby optimizing the trade-off between redundancy elimination and query performance.
2Reliability
If complex JOIN queries are executed on normalized databases to retrieve data from multiple tables, then data completeness and accuracy are improved, but processing stress on hardware and CPU usage increase
Solution Approach 1:
The patent performs preliminary actions by pre-computing and storing denormalized versions of frequently queried data combinations. Before actual queries are executed, the system maintains pre-joined tables that contain data from multiple normalized tables already combined, so that queries can retrieve complete and accurate data without performing computationally intensive JOIN operations at query time.
Solution Approach 2:
The patent creates copies of normalized data in denormalized formats, where each copy is optimized for specific query patterns. Instead of executing complex JOINs on the original normalized tables, the system queries these pre-computed copies, which contain the same data accuracy but require significantly less processing power to retrieve.
3Quantity of substance
If data is stored in highly normalized form with many small tables, then storage efficiency and data integrity are improved, but the complexity of query execution and data retrieval increases
Solution Approach 1:
The patent segments the complex query execution process into multiple simpler stages by using denormalized tables that are each optimized for specific query types. Instead of executing a single complex query that must navigate multiple normalized tables and JOINs, the system divides queries into simpler operations against specialized denormalized tables, reducing the overall execution complexity while maintaining storage efficiency.
Solution Approach 2:
The patent introduces denormalized tables as intermediary structures between the normalized storage layer and the query execution layer. These intermediary tables absorb the complexity of data retrieval by pre-combining data from multiple normalized tables, thereby simplifying the query execution process while preserving the storage efficiency benefits of normalization.
Data Source
AI summary
Computer implemented methods for executing a database query in a database using an electronic computing device are presented, the method including: causing the electronic computing device to provide at least one data structure including at least one object having at least two properties of different data types; identifying a property of the at least one data structure that includes the smallest unique values in terms of type of data stored in that particular property; executing the database query, including any limiting parameters, configured to retrieve only data from the property including the smallest unique values that are retrievable for a record; retrieving a predefined number of results of the executed database query; and for each set of predefined number of retrieved results, executing a new database access thread that is configured to retrieve data that are present in the records identified with the smallest unique values.