Database Query Optimization via Term Tables and Rewriting
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database query systems face inefficiencies, particularly when performing full table scans and multiple joins, leading to slow query execution times and high resource usage, especially for queries involving CONTAINS, left join, and OR expressions.
Innovation Solution
The approach involves rewriting query instructions to use alternative forms such as inner joins and UNION expressions, creating term tables for efficient record selection, and optimizing left join expressions to reduce resource consumption and improve query speed.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If full table scans are performed to access subsets of records, then all records can be reviewed, but query execution time increases substantially
Solution Approach 1:
The system performs preliminary actions by creating and maintaining term tables that pre-index records based on their content. When a query is executed, the system can directly access these pre-organized term tables instead of scanning the entire source table, thus retrieving the required subset of records much faster while still ensuring completeness of the review.
2Reliability
If multiple joins are performed to access related records, then comprehensive data can be retrieved, but resource usage increases significantly
Solution Approach 1:
The system extracts the essential linking information from multiple joined tables and consolidates it into a single term table. This term table contains pre-computed relationships and identifiers that allow the system to retrieve comprehensive data from multiple sources without actually performing multiple join operations, thereby significantly reducing CPU and memory resource usage.
3Reliability
If left join expressions are used to preserve all records from the left table, then no records are lost, but query complexity and execution time increase
Solution Approach 1:
The system creates a copy of the left table's record identifiers and their associated term table references. By copying just the essential linking information rather than performing actual join operations, the system preserves all records from the left table while simplifying the query structure to a straightforward term table lookup, reducing both complexity and execution time.
4Reliability
If OR expressions are used to search multiple conditions, then all matching records can be found, but query execution becomes slower
Solution Approach 1:
The system segments the OR expression by creating separate term table lookups for each condition in the OR expression. Instead of evaluating multiple conditions sequentially or using complex OR logic, the system queries each term table independently and combines the results, which leverages the optimized lookup structure of term tables to find all matching records much faster.
Data Source
AI summary
The present approach relates to improving query performance in a database context. Examples of query improvement are described in the context of certain query patterns, one or more of which may be observed in a given query. When a given query pattern is observed, changes may be made to the query at the application or database level to improve performance of the respective query. Query improvements may be performed in a manner transparent to the user.