SQL Query Similarity Grouping Using ASTs and LSH
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Identifying similar SQL queries in large databases with millions of queries is challenging due to the computational complexity and difficulty in analyzing vast amounts of data, making it difficult to determine exact or similar queries efficiently.
Innovation Solution
A distributed computing methodology using bitwise summaries and Locally Sensitive Hashing (LSH) algorithms to represent SQL queries as binary vectors, enabling fast and interactive comparison of tens of millions of queries, with techniques like MinHash and LSH reducing the search space and identifying groupings of similar queries.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If traditional SQL query comparison methods are used, then query analysis accuracy is maintained, but computational time and resource consumption increase exponentially with database size
Solution Approach 1:
The patent segments the query comparison process into multiple stages: first converting SQL queries into standardized abstract syntax trees (ASTs), then into fixed-length binary vectors, and finally comparing these vectors using Locally Sensitive Hashing. This segmentation transforms an intractable O(n²) string comparison problem into a manageable multi-stage process that achieves linear or near-linear scalability.
Solution Approach 2:
The patent introduces intermediary representations between raw SQL queries and final similarity comparisons. Specifically, it uses ASTs as an intermediate structured representation that captures query semantics, and binary vectors as another intermediate form that enables efficient mathematical operations. These intermediaries act as mediators that preserve query meaning while enabling scalable comparison.
2Measurement precision
If exact duplicate detection is performed on millions of queries, then identification accuracy is high, but the system becomes unresponsive and computation becomes infeasible
Solution Approach 1:
The patent changes the parameter space from raw SQL text to standardized AST structures, then to fixed-length binary vectors. This parameter transformation preserves the semantic information needed for accurate similarity detection while converting the data into a form suitable for high-speed computational processing and hashing operations.
Solution Approach 2:
The patent performs preliminary actions by pre-converting SQL queries into ASTs and binary vectors before the actual similarity comparison. This preprocessing step, including normalization of query structures and computation of binary representations, is done in advance to enable rapid comparison operations when similarity detection is needed.
3Loss of information
If comprehensive query pattern analysis is conducted on large datasets, then database optimization insights are improved, but computational overhead becomes unmanageable
Solution Approach 1:
The patent extracts only the essential structural and semantic features from SQL queries by converting them to ASTs and then to binary vectors that capture key query characteristics. This extraction process removes unnecessary textual variations and focuses computation on the meaningful elements that determine query similarity and data access patterns.
Solution Approach 2:
The patent creates simplified copies of the original SQL queries in the form of binary vectors that preserve the essential information needed for pattern analysis. These binary vector copies enable efficient computation and comparison while consuming minimal computational resources compared to processing the full SQL text.
Data Source
AI summary
Systems and methods for very fast grouping of “similar” SQL queries according to user-supplied similarity criteria. The user-supplied similarity criteria include a threshold quantifying the degree of similarity between SQL queries and common artifacts included in the queries. A similarity-characterizing data structure allows for the very fast grouping of “similar” SQL queries. Because the computation is distributed among multiple compute nodes, a small cluster of compute nodes takes a short time to compute the similarity-characterizing data on a workload of tens of millions of queries. The user can supply the similarity criteria through a UI or a command line tool. Furthermore, the user can adjust the degree of similarity by supplying new similarity criteria. Accordingly, the system can display in real time or near real time, updated SQL groupings corresponding to the newly supplied similarity criteria using the originally computed similarity-characterizing data structure.