Database Query Deduplication via Hash-Based Operator Tree Refactoring
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database engines face challenges in optimizing complex queries, leading to significantly different execution times for the same query, and struggle to efficiently remove duplicate query fragments, which affects performance.
Innovation Solution
The database engine employs a method that translates SQL queries into abstract syntax trees, performs semantic analysis, and applies optimization techniques such as constant folding and predicate pushdown, while removing duplicate subqueries through operator tree refactoring and deduplication optimization passes, using techniques like depth-first and breadth-first traversals and hash-based deduplication to eliminate redundant operations.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Loss of time
If the database engine executes complex queries with duplicate subqueries using traditional optimization methods, then the query can be processed, but the execution time becomes excessively long due to redundant query operations
Solution Approach 1:
The patent extracts and identifies duplicate subquery fragments from the query operator tree using hash-based comparison. By detecting equivalent query operators through traversal and hashing, the system isolates redundant portions that can be eliminated, directly reducing execution time while maintaining processing capability.
Solution Approach 2:
The patent merges duplicate query operator trees by replacing redundant operators with references to existing ones. Through tree traversal and equivalence detection, identical subqueries are consolidated into single execution instances, improving productivity by eliminating repetitive processing while preserving the complete query logic.
2Productivity
If the database engine applies comprehensive optimization techniques to complex queries, then query processing efficiency improves, but the complexity of the optimization process increases
Solution Approach 1:
The patent segments the optimization process into distinct phases: parsing SQL to abstract syntax trees, converting to operator trees, performing semantic analysis, and applying optimization passes. This segmentation manages complexity by breaking down the comprehensive optimization task into manageable, sequential steps while maintaining high processing efficiency.
Solution Approach 2:
The patent introduces intermediate representations (abstract syntax trees and operator trees) as mediators between the input SQL and the execution plan. These intermediate structures facilitate systematic optimization through multiple passes while keeping the overall process manageable, balancing productivity improvement with controlled complexity.
3Loss of time
If the database engine removes duplicate query fragments through advanced optimization passes, then execution time is reduced, but the device complexity increases due to additional optimization layers
Solution Approach 1:
The patent uses hash-based copying and comparison of query operator representations to identify duplicates. By creating hash representations of query operators and comparing them, the system efficiently detects equivalent subqueries without requiring complex structural analysis, reducing execution time while adding minimal engine complexity.
Solution Approach 2:
The patent changes the representation parameters of query operators by converting SQL to abstract syntax trees, then to operator trees with specific properties. This parameter transformation enables systematic duplicate detection through hash comparison, achieving execution time reduction with controlled increases in engine complexity through standardized representation changes.
Data Source
Figure 1
Figure 2
Figure 3A
AI summary
A database engine receives a database query from a client. The database engine parses the database query to build a query operator tree that includes a plurality of query operators. The database engine performs one or more optimization passes on the query operator tree, including a deduplication optimization pass, to form an optimized execution plan. The deduplication optimization pass includes: creating a list of query operators via a first traversal of the query operator tree, determining a first query operator that is equivalent to a second query operator, based on a hash map, via a second traversal of the query operator tree, and substituting, via a third traversal of the query operator tree, the second query operator with a tree node that links to the first query operator. The database engine executes the optimized execution plan to retrieve a result set from the database, and returns the result set.