Aggregate Query Processing with Varying Grain Sizes
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing query processing systems face inefficiencies when handling time series data with varying time zones and granularities, as they often require hourly aggregates even when daily aggregates are sufficient, leading to increased read operations and reduced performance.
Innovation Solution
The system maintains aggregates based on multiple grain sizes, including fine and coarse grains, and adjusts queries by subtracting or adding portions of fine grain aggregates to maximize the use of coarse grain aggregates, minimizing read operations through an exclusion threshold and optimized read range determination.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If hourly aggregates are used for all queries regardless of time zone, then query accuracy is maintained, but read operations increase and performance decreases
Solution Approach 1:
The patent segments the aggregate data into multiple grain sizes (hourly, daily, weekly, monthly) and selectively applies different grain sizes based on the query's time zone and time grain requirements. This segmentation allows the system to use coarser aggregates when appropriate, reducing read operations while maintaining accuracy where needed.
Solution Approach 2:
The patent dynamically selects the appropriate aggregate grain size based on the query parameters (time zone, time grain). The system adapts its behavior by determining whether to use hourly or daily aggregates based on the specific query context, rather than statically using hourly aggregates for all queries.
2Productivity
If daily aggregates are used when query granularity is a day, then read operations are reduced, but queries in different time zones or with non-standard day starts cannot use daily aggregates
Solution Approach 1:
The patent adds a new dimension to the aggregate selection process by considering the relationship between query time zone, query time grain, and aggregate computation time zone. This multi-dimensional approach allows the system to determine when daily aggregates can be used even for non-GMT time zones by checking if the query time grain is coarser than or equal to one day.
Solution Approach 2:
The patent changes the parameters used for aggregate selection from a simple time grain check to a comprehensive evaluation including time zone offset, query time grain, and aggregate computation time zone. This parameter expansion enables more flexible use of daily aggregates across different time zones.
3Adaptability or versatility
If hourly aggregates are used to handle time zone differences, then time zone flexibility is maintained, but the number of read operations increases
Solution Approach 1:
The patent performs preliminary evaluation of query parameters (time zone offset, time grain, aggregate computation time zone) before executing the query. This preliminary action determines whether daily aggregates can be used, avoiding the need to read hourly aggregates when daily aggregates are sufficient, thus reducing query processing time.
Solution Approach 2:
The patent replaces the mechanical approach of always reading hourly aggregates with an intelligent selection mechanism that substitutes daily aggregates when appropriate. This substitution reduces the amount of data that needs to be read and processed, improving query performance.
Data Source
AI summary
A computer-implemented method and system for querying aggregates in a database include maintaining aggregates based on a dimension in the database with at least two grain sizes; receiving a query of the aggregates for a defined range of the dimension; finding a start and an end for a read operation for a larger grain size of the at least two grain sizes of the aggregates for the defined range; reading a first set from the start to the end in the database of the larger grain size of the at least two grain sizes of the aggregates; reading a second set comprising a smaller grain size of the at least two grain sizes of the aggregates based on the defined range and the start and the end; and adjusting the first set with the second set.