Auto-Injected Query Filter for Database Performance
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Enterprise software systems face prolonged waiting times due to complex or resource-intensive queries, particularly when large result sets are returned, leading to unacceptable user experience.
Innovation Solution
Implementing automatic query filtering based on metadata such as row cardinality and table size before submission to the backend, limiting queries to prevent excessively time-consuming executions by injecting filters over computationally expensive dimensions.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Loss of information
If complex or resource-intensive queries are executed against large datasets, then comprehensive data analysis is achieved, but waiting time increases proportionally
Solution Approach 1:
The system performs preliminary actions by automatically generating and executing multiple filtered queries in parallel before the user completes their query formulation. These preliminary queries explore different facets of the data (e.g., time trends, category distributions) and prepare visualizations in advance, so when the user submits their final query, results are already available or nearly ready, dramatically reducing perceived waiting time while maintaining data completeness.
Solution Approach 2:
The system applies partial action by executing a subset of potentially useful queries in advance based on metadata analysis. Instead of waiting for the complete user query, the system identifies high-value data explorations that are likely to be useful and executes them partially or fully beforehand, providing early visualizations that can be refined or replaced when the actual query results arrive.
2Quantity of substance
If queries return large result sets, then comprehensive data coverage is achieved, but processing time and resource consumption increase
Solution Approach 1:
The system segments large result sets by automatically dividing queries into multiple smaller, parallel-executable subqueries based on metadata analysis. Each subquery processes a manageable portion of the data independently, and results are aggregated afterward. This segmentation maintains comprehensive data coverage while dramatically improving processing speed by leveraging parallel execution and reducing the computational burden on any single query.
Solution Approach 2:
The system dynamically changes query parameters based on metadata characteristics. When metadata indicates large datasets or complex queries, the system automatically adjusts parameters such as result set limits, aggregation levels, or filtering criteria to optimize processing speed while maintaining data representativeness. This allows the system to return smaller, faster results that still provide comprehensive insights.
3Loss of time
If automatic query filtering is applied based on metadata, then query execution time is reduced, but query flexibility may be limited
Solution Approach 1:
The filtering system is fully dynamic and adaptive rather than static. It continuously monitors query metadata, user behavior patterns, and system performance metrics to adjust filtering strategies in real-time. The filtering thresholds, applied filters, and parallel query configurations are dynamically determined based on the specific characteristics of each query and the current system state, ensuring optimal balance between speed and flexibility for every query while maintaining user control.
Data Source
AI summary
A system includes reception, from a user, elements of a database query including a dimension and a data source, determination of metadata of the data source, the metadata describing a domain of dimension values of the dimension, determination of whether to filter the database query based on the metadata, and, if it is determined to filter the database query, automatic generation of a filtered database query to limit the domain of the dimension values of a corresponding result set to less than the domain of dimension values of the dimension.