Shared Cache Result Set Retrieval for Database Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems face performance issues due to high CPU usage and input/output operations when processing SQL queries, as they involve fetching rows from tables, which can require thousands of CPU instructions, and existing solutions like in-memory tables and caching do not always provide desired query performance.
Innovation Solution
Implementing a shared cache system where processor units process queries, store result sets, and return them from a shared cache assigned to a group of clients, either on the client-side or server-side, reducing the need for repeated database searches and input/output operations by using a mapping or hash table to locate result sets.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If rows are fetched from database tables using sequential SQL queries, then query results can be obtained, but CPU usage increases significantly due to thousands of instructions required per row
Solution Approach 1:
The patent applies preliminary action by pre-computing query results and storing them in a result cache before they are actually needed. When a query is received, the system first checks the result cache for pre-computed results matching the query parameters, avoiding the need to execute the full query processing pipeline again. This eliminates the thousands of CPU instructions that would otherwise be required to fetch and process rows from database tables, directly resolving the contradiction between query processing speed and CPU usage.
2Loss of energy
If in-memory tables are used instead of disk-based tables, then I/O costs are reduced, but processor usage costs increase
Solution Approach 1:
The patent extracts the result set from the database table and stores it separately in a result cache (memory structure). This separation allows the system to avoid repeated I/O operations to fetch the same data while also avoiding the need to keep entire tables in memory. Only the specific query results are cached, not the entire table, thus reducing both I/O costs and processor usage compared to using in-memory tables.
3Productivity
If materialized views or caching is used to improve SQL query performance, then query speed increases, but the desired level of performance is not always achieved
Solution Approach 1:
The patent applies local quality by implementing caching at the specific location where query results are most needed - in the database server's result cache - rather than relying on external caching mechanisms. The cache is integrated into the query processing pipeline, allowing direct access to cached results before returning them to clients. This localized approach simplifies the overall system architecture compared to distributed caching solutions while achieving the desired performance improvement.
Data Source
AI summary
A computer implemented method processes a query. A number of processor units processes the query to identify a result set in response to receiving the query from a first client. The number of processor units stores, the result set in a shared cache assigned to a group of clients, wherein result set stored in the shared cache is accessible by the group of clients. The number of processor units returns the result set to a second client in the group of clients from the shared cache in response to receiving the query from the second client in the group of clients.