Transactional Database Scanning for Query Performance
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
As data sets grow in size and scale, the costs of searching, analyzing, and managing them increase, with query operations becoming time-consuming and resource-intensive, necessitating techniques to reduce performance costs without upgrading underlying resources.
Innovation Solution
Implementing transactional scanning of database portions, leveraging transaction guarantees for Atomicity, Consistency, and Isolation to share the costs of scanning amongst multiple records, allowing batches or groups of records to be scanned within individual transactions, thereby reducing overhead and processing costs.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If traditional scanning operations are performed on large data sets, then complete data coverage is achieved, but time consumption and resource utilization increase significantly
Solution Approach 1:
The patent divides the database into multiple partitions and scans only relevant portions based on query conditions. Each partition is scanned independently using transactional scanning, allowing the system to process only the necessary data segments rather than performing full database scans, thus reducing time consumption while maintaining complete coverage of relevant data.
Solution Approach 2:
The system performs partial scanning by identifying and scanning only the portions of the database that are relevant to the query conditions. By using transactional scanning with partition pruning, the system avoids scanning unnecessary data portions, achieving time reduction while ensuring all relevant records are captured through the transactional guarantee.
2Reliability
If traditional scanning operations are performed on large data sets, then all records are processed, but resource utilization increases significantly
Solution Approach 1:
The database is segmented into partitions, and the scanning operation is divided across multiple transactional contexts. Each transaction scans a specific partition or subset of partitions, reducing the memory and CPU resources required for each individual scanning operation while ensuring all relevant records are processed through the union of these segmented scans.
Solution Approach 2:
The system performs partial scanning by selectively accessing only the partitions that contain data matching the query conditions. This approach reduces resource utilization by avoiding the processing overhead of scanning entire data sets, while the transactional scanning mechanism ensures that all relevant records across the selected partitions are completely processed.
3Productivity
If scanning costs are shared amongst multiple records through transactions, then overhead is reduced, but transaction management complexity increases
Solution Approach 1:
The transactional scanning mechanism serves multiple functions simultaneously: it provides atomicity for consistent reads, enables cost sharing across multiple records through the transaction context, and facilitates partition pruning for optimized scanning. This multi-functionality reduces overall scan operation overhead while the database engine's built-in transaction management handles the complexity internally, presenting a simplified interface to users.
Data Source
AI summary
Transactional scans of portions of a database may be performed. When a query is received that causes a scan of a database, different portions of the database may be scanned within separate transactions. Operations applicable to perform the scan the records of the different portions of the database may be invoked once within the transactions instead of invoking the operations individually for each of the records in the different portions. Results of the query based on the scanned portions may then be provided in response to the query.