Database Query Optimization via Storage Compression
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database engines face challenges in optimizing certain types of queries, leading to significantly different execution times for the same SQL queries, resulting in inefficient data retrieval.
Innovation Solution
The implementation optimizes domain queries by parsing database queries to build an operator tree, identifying TableScan and GroupBy operators, and using storage compression schemes like dictionary, single-value, and frame of reference encoding to reduce or eliminate duplicate values during data retrieval, thereby improving query execution efficiency.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If traditional database query optimization is used, then general query processing is supported, but domain queries with TableScan and GroupBy operators execute slowly due to duplicate value processing
Solution Approach 1:
The patent extracts and identifies duplicate values from the data stream during TableScan operation, separating them into a distinct set. This extracted duplicate information is then reused by the GroupBy operator to avoid redundant processing, directly resolving the contradiction by removing the harmful duplicate processing while maintaining general query support
Solution Approach 2:
The patent performs preliminary identification and storage of duplicate values during the TableScan phase, before the GroupBy operator processes the data. This preliminary action of pre-computing and storing duplicate information allows the GroupBy operator to skip redundant work, improving query execution speed while maintaining compatibility with general query processing
2Productivity
If storage compression schemes are applied to eliminate duplicates, then query execution efficiency improves, but memory usage and storage requirements increase
Solution Approach 1:
The patent applies compression schemes locally only to the specific data structures that benefit from them (e.g., duplicate value sets, domain queries with TableScan and GroupBy), rather than universally to all data. This selective application improves retrieval speed for relevant queries while minimizing the overall memory overhead, as compression structures are created only where needed
Solution Approach 2:
The patent changes the parameter of data representation by applying various compression schemes (dictionary encoding, run-length encoding, etc.) to transform the storage format of duplicate values. This parameter change allows efficient storage and retrieval of compressed data, improving query execution speed while reducing the quantity of stored information compared to uncompressed formats
Data Source
AI summary
A database engine receives a database query that specifies retrieving data from a data source. The database engine parses the query to build an operator tree that includes a TableScan operator configured to scan a table from the data source to produce outputs corresponding to a single data field from the table, and includes a GroupBy operator that groups rows of the table according to the data field. The database engine generates and executes code corresponding to the operator tree to retrieve a result set. When the TableScan operator is a child of the GroupBy operator and the outputs are independent of duplicate input rows from the table, execution of the TableScan operator comprises, for each storage block of rows from the table: determining a storage compression scheme for encoding the data field and, for certain encodings, using the encoding to produce the outputs without duplication of rows.